Главная Power Query Power Query. Информация о формате, Чтение zip
Power QueryPower Query. Разное

Power Query. Информация о формате, Чтение zip

Файлы к уроку:

Ссылки:

Описание

В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.

По умолчанию при помощи Power Query вы не можете зацепиться за формат ячеек. Вам доступны только данные в этих ячейках, но информации об отступах, цвете, шрифте и т. д. у вас нет.

В этом уроке нам нужно обработать файл, в котором кроме как за формат ячеек больше не за что зацепиться.

Решение

Что такое файл Excel? Файл Excel — это zip-архив, внутри которого находится много XML файлов. XML-разметка содержит в себе информацию не только о данных, но и о форматах. Получается, что если мы откроем XML-файл, соответствующий нужному нам листу, то сможем получить данные о форматах.

Как же извлечь XML из xlsx при помощи Power Query, если Power Query не умеет читать архивы? На самом деле в Power Query есть множество функций, которые нужны для чтения архивов. Чтобы грамотно пользоваться этими функциями нужно иметь знания об архивах. У меня таких знаний нет, что мне мне мешает воспользоваться чужими знаниями? Кен Р. написал функцию для чтения zip-архивов. Этой функцией мы и воспользуемся.

Мы подключимся к Excel-файлу 2 раза. Прочитаем файл обычным способом и прочитаем его функцией Кена Р., чтобы получить форматы. Получившиеся 2 таблицы нужно будет объединить по горизонтали.

Примененные функции

  • File.Contents
  • BinaryFormat.Record
  • BinaryFormat.Binary
  • BinaryFormat.ByteOrder
  • BinaryFormat.UnsignedInteger32
  • ByteOrder.LittleEndian
  • BinaryFormat.Text
  • Binary.Decompress
  • Table.FromRecords
  • Xml.Tables
  • Table.SelectColumns
  • Table.ExpandTableColumn
  • Table.SelectRows
  • Text.Contains
  • Table.AddIndexColumn
  • Table.NestedJoin
  • JoinKind.LeftOuter
  • Table.FillDown
  • Table.PromoteHeaders
  • Table.RenameColumns

Код

Получаем первую таблицу с данными о свойствах ячеек:

let
   Source = File.Contents(path),
   DecompressFiles = (ZIPFile, Position, FileToExtract, DataSoFar) =>
      let
         MyBinaryFormat = try
            BinaryFormat.Record(
               [
                  DataToSkip = BinaryFormat.Binary(Position),
                  MiscHeader = BinaryFormat.Binary(18),
                  FileSize = BinaryFormat.ByteOrder(
                     BinaryFormat.UnsignedInteger32,
                     ByteOrder.LittleEndian
                  ),
                  UnCompressedFileSize = BinaryFormat.Binary(4),
                  FileNameLen = BinaryFormat.ByteOrder(
                     BinaryFormat.UnsignedInteger16,
                     ByteOrder.LittleEndian
                  ),
                  ExtrasLen = BinaryFormat.ByteOrder(
                     BinaryFormat.UnsignedInteger16,
                     ByteOrder.LittleEndian
                  ),
                  TheRest = BinaryFormat.Binary()
               ]
            )
         otherwise
            null,
         MyCompressedFileSize = try
            MyBinaryFormat(ZIPFile)[FileSize] + 1
         otherwise
            null,
         MyFileNameLen = try
            MyBinaryFormat(ZIPFile)[FileNameLen]
         otherwise
            null,
         MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,
         MyBinaryFormat2 = try
            BinaryFormat.Record(
               [
                  DataToSkip = BinaryFormat.Binary(Position),
                  Header     = BinaryFormat.Binary(30),
                  Filename   = BinaryFormat.Text(MyFileNameLen),
                  Extras     = BinaryFormat.Binary(MyExtrasLen),
                  Data       = BinaryFormat.Binary(MyCompressedFileSize),
                  TheRest    = BinaryFormat.Binary()
               ]
            )
         otherwise
            null,
         MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null,
         GetDataToDecompress = try
            MyBinaryFormat2(ZIPFile)[Data]
         otherwise
            null,
         DecompressData = try
            Binary.Decompress(GetDataToDecompress, Compression.Deflate)
         otherwise
            null,
         NewPosition = try
            Position
               + 30
               + MyFileNameLen
               + MyExtrasLen
               + MyCompressedFileSize
               - 1
         otherwise
            null,
         AsATable = Table.FromRecords(
            {[Filename = MyFileName, Content = DecompressData]}
         ),
         #"Appended Query" = 
            if DecompressData = null then
               DataSoFar
            else if (MyFileName = FileToExtract) then
               AsATable
            else if (FileToExtract = "") and Position <> 0 then
               Table.Combine({DataSoFar, AsATable})
            else
               AsATable
      in
         if (MyFileName = FileToExtract) or (#"Appended Query" = DataSoFar) then
            #"Appended Query"
         else
            @DecompressFiles(
               ZIPFile,
               NewPosition,
               FileToExtract,
               #"Appended Query"
            ),
   MyData = DecompressFiles(Source, 0, "", null),
   Content = MyData{[Filename = "xl/worksheets/sheet1.xml"]}[Content],
   import_xml = Xml.Tables(Content, null, 1251),
   open_sheetdata = import_xml{[Name = "sheetData"]}[Table],
   open_table = open_sheetdata{0}[Table],
   cols_select = Table.SelectColumns(open_table, {"c"}),
   col_expand = Table.ExpandTableColumn(
      cols_select,
      "c",
      {"Attribute:r", "Attribute:s", "v", "Attribute:t"},
      {"Attribute:r", "Attribute:s", "v", "Attribute:t"}
   ),
   rows_select = Table.SelectRows(
      col_expand,
      each Text.Contains([#"Attribute:r"], "A")
   ),
   table_add_index = Table.AddIndexColumn(rows_select, "Индекс", 0, 1)
in
   table_add_index

Получаем вторую таблицу и объединяем ее с первой:

let
   source = Excel.Workbook(File.Contents(path), null, true),
   get_sheet = source{[Item = "Лист1", Kind = "Sheet"]}[Data],
   table_index_col = Table.AddIndexColumn(get_sheet, "Индекс", 0, 1),
   tables_join = Table.NestedJoin(
      table_index_col,
      {"Индекс"},
      Числа,
      {"Индекс"},
      "Числа",
      JoinKind.LeftOuter
   ),
   col_expand_nums = Table.ExpandTableColumn(
      tables_join,
      "Числа",
      {"Attribute:s"},
      {"Attribute:s"}
   ),
   table_add_col_1 = Table.AddColumn(
      col_expand_nums,
      "Пользовательская",
      each if [#"Attribute:s"] = "8" then [Column1] else null
   ),
   table_add_col_2 = Table.AddColumn(
      table_add_col_1,
      "Пользовательская.1",
      each if [#"Attribute:s"] = "11" then "Поставщик 1" else null
   ),
   table_add_col_3 = Table.AddColumn(
      table_add_col_2,
      "Пользовательская.2",
      each if [#"Attribute:s"] = "12" then "Поставщик 2" else null
   ),
   col_fill_down = Table.FillDown(table_add_col_3, {"Пользовательская"}),
   rows_select_1 = Table.SelectRows(
      col_fill_down,
      each ([Column2] <> null)
   ),
   table_promote_headers = Table.PromoteHeaders(
      rows_select_1,
      [PromoteAllScalars = true]
   ),
   table_rename_cols = Table.RenameColumns(
      table_promote_headers,
      {
         {"Column7", "Регион"},
         {"Column8", "Поставщик 1"},
         {"Column9", "Поставщик 2"}
      }
   ),
   rows_select_2 = Table.SelectRows(
      table_rename_cols,
      each ([Группа аналитического учета] <> "Итого")
   ),
   cols_select = Table.SelectColumns(
      rows_select_2,
      {
         "Регион",
         "Поставщик 1",
         "Поставщик 2",
         "Выручка",
         "Валовая прибыль",
         "Рентабельность, %"
      }
   )
in
   cols_select

Power Query разное

Номер урокаУрокОписание
1Power Query. Знакомство с Power QueryВ этом уроке мы познакомимся в Power Query.
Зачем нужен Power Query
Как установить Power Query
Как его Настроить
Как изменить запрос
2Power Query. Подключение XMLВ этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel.
3Power Query. Уникальные значения двух столбцовВ этом уроке мы получим уникальные значения из двух столбцов таблицы.
4Power Query. Импорт таблиц PDFИмпорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет.
5Power Query. Собрать разбитую строкуВ этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала.
6Power Query. Пивот со счетомВ этом уроке мы создадим пивот, в котором будут пронумерованы столбцы.
7Power Query. Минимальное значение в диапазонеВ этом уроке мы найдем минимальное значение в диапазоне строк.
8Power Query. Нарастающий итог 2В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query.
9Power Query. Нарастающий итог 3В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query.
10Power Query. Прирост населения КитаяВ этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет.
11Power Query. Повторяющиеся значения в строкеВ этом уроке разберем как определить есть ли в строке повторения.
12Power Query. Таблица навигации по функциям МВ этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query.
13Power Query. Удалить запросы и модель данных из книгиРазберем как быстро удалить все запросы и модель данных из текущей книги.
14Power Query. Открыть еще 1 Excel и еще 3 трюкаВ этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query.
15Power Query. Подключиться к ZIP архивуПользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве.
16Power Query. Импорт WordИмпортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками.
17Power Query. Фильтрация спискомВ этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров.
18Power Query. Пользовательская функция SwitchВ этом уроке мы создадим пользовательскую функцию Switch.
19Power Query. Информация о формате, Чтение zipВ этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
20Power Query. Импорт данных из gzВ этом уроке мы разберем как импортировать файл в формате gz.
21Power Query. Удалить лишние пробелы, Text.SplitВ этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы.
22Power Query. Параметры в SQL-запросеВы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel.
23Power Query. Параметры в SQL-запросе 2Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать.
24Power Query. Добавить столбец в каждую таблицу табличного столбцаВ этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца.
25Power Query. Интервальный просмотр 1 (ВПР 1)Объединить 2 таблицы с интервальным просмотром = 1.
26Power Query. Относительный путь к файлу и папкеЕсли ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере.
27Power Query. Нарастающий итог в каждой категорииПрименим функцию нарастающего итога не ко всей таблице, а к определенному окну.
28Power Query. ВПР без Merge или JoinВам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.

Вас может заинтересовать

Power Query. Пользовательская функция Switch

Power Query. Обработка PDF выписки

Power Query Практический №15. List Contains, Пользовательская функция

Power Query Продвинутый №7. Объект Record (Запись)

Power Query Веб-запросы №15. Запрос к XML Sitemap (Карта сайта)

Power Query Продвинутый №9. Ссылки 1

Power Query Практический №16. ABC анализ

Power Query Ошибки №3. Formula Firewall, запрос ссылается на…

Power Query + VBA №3. Обработать и сохранить много файлов

Power Query. Таблица навигации по функциям М