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

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

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

В этом уроке мы узнаем как получить информацию о формате ячеек при помощи 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 разное

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