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

XML

предыдущая запись

Power Query. Практика. Нарастающий итог 4, Группировки

следующая запись

Power Query. Формулы М. Информация о формате, чтение zip

Описание

Решение

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

Код

Power Query. Практика. Нарастающий итог 4, Группировки

Power Query. Ошибки. Доступ к веб-содержимому

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