Power Query. Импорт Word

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке вы узнаете как импортировать таблицы из Word документа прямо на лист Excel с помощью Power Query.

Сложность заключается в том, что стандартного коннектора для Word в данный момент нет.

В бонусной части урока вы узнаете как это сделать, если в исходной Word таблице есть объединенные ячейки.

Решение

В решении нам пригодится функция Кена Р. под названием DecompressFiles. Эта функция нужна для работы с архивами, а так как docx документ — это zip архив, то здесь она как раз будет полезна.

После применения данной функции нам останется только прочитать результат как xml и привести полученную таблицу к нужному виду.

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

• BinaryFormat.Record
• BinaryFormat.Binary
• BinaryFormat.ByteOrder
• BinaryFormat.UnsignedInteger32
• ByteOrder.LittleEndian
• BinaryFormat.UnsignedInteger16
• Binary.Decompress
• Compression.Deflate
• File.Contents
• Xml.Document
• Table.SelectRows
• Table.AddIndexColumn
• Int64.Type
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.AddColumn
• Table.ReplaceValue
• List.Repeat
• Replacer.ReplaceValue
• Table.ExpandListColumn
• Table.RemoveColumns
• Table.Group
• Table.Pivot
• Table.TransformColumnTypes
• List.Distinct
• Table.Transpose
• Table.CombineColumns
• Combiner.CombineTextByDelimiter
• QuoteStyle.None
• Table.PromoteHeaders
• Table.RenameColumns
• Text.Start
• Table.UnpivotOtherColumns
• Table.IsEmpty
• Value.Is
• Table.Type
• List.Type
• Record.Type
• Text.Combine

Код

Код функции Decompress_Files автора KenR:

(ZIPFile, Position, FileToExtract, XMLSoFar) =>
   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,
      #"Imported XML" = DecompressData,
      //#"Imported XML" = DecompressData ,
      #"Added Custom" = try
         Table.AddColumn(#"Imported XML", "Filename", each MyFileName)
      otherwise
         #"Imported XML",
      #"Appended Query" = 
         if #"Imported XML" = null then
            XMLSoFar
         else if (MyFileName = FileToExtract) then
            #"Added Custom"
         else if (FileToExtract = "") and Position <> 0 then
            Table.Combine({#"Added Custom", XMLSoFar})
         else
            #"Added Custom"
   in
      if (MyFileName = FileToExtract) or (#"Appended Query" = XMLSoFar) then
         #"Appended Query"
      else
         @fn_decompress_files(
            ZIPFile,
            NewPosition,
            FileToExtract,
            #"Appended Query"
         )

Код функции для получения данных из ячеек:

(table_obj) =>
   let
      rows_select_5 = Table.SelectRows(table_obj, each ([Name] = "p")),
      col_replace_val_1 = Table.ReplaceValue(
         rows_select_5,
         each [Value],
         each Table.SelectRows([Value], each [Name] = "r"),
         Replacer.ReplaceValue,
         {"Value"}
      ),
      col_replace_val_2 = Table.SelectRows(
         col_replace_val_1,
         each not Table.IsEmpty([Value])
      ),
      cols_select_2 = Table.SelectColumns(col_replace_val_2, {"Value"}),
      col_expand_2 = Table.ExpandTableColumn(
         cols_select_2,
         "Value",
         {"Value"},
         {"Value.1"}
      ),
      col_replace_val_3 = Table.ReplaceValue(
         col_expand_2,
         1,
         1,
         Replacer.ReplaceValue,
         {"Value.1"}
      ),
      col_replace_val_4 = Table.ReplaceValue(
         col_replace_val_3,
         each [Value.1],
         each Table.SelectRows([Value.1], each [Name] = "t"),
         Replacer.ReplaceValue,
         {"Value.1"}
      ),
      col_expand_3 = Table.ExpandTableColumn(
         col_replace_val_4,
         "Value.1",
         {"Value"},
         {"Value"}
      ),
      col_isarray = Table.AddColumn(
         col_expand_3,
         "Is Array",
         each Value.Is([Value], Table.Type)
            or Value.Is([Value], List.Type)
            or Value.Is([Value], Record.Type)
      ),
      rows_select_6 = Table.SelectRows(
         col_isarray,
         each ([Is Array] = false)
      ),
      text_combine = Text.Combine(rows_select_6[Value], " ")
   in
      text_combine

Код запроса для получения таблицы:

let
   path = Excel.CurrentWorkbook(){[Name = "file_path_1"]}[Content]{0}[
      Column1
   ],
   Source = File.Contents(path),
   MyXML = fn_decompress_files(Source, 0, "word/document.xml", ""),
   read_xml = Xml.Document(MyXML),
   open_table_1 = read_xml{0}[Value],
   open_table_2 = open_table_1{0}[Value],
   rows_select_1 = Table.SelectRows(open_table_2, each ([Name] = "tbl")),
   open_table_3 = rows_select_1{0}[Value],
   rows_select_2 = Table.SelectRows(open_table_3, each ([Name] = "tr")),
   col_index = Table.AddIndexColumn(
      rows_select_2,
      "Индекс",
      1,
      1,
      Int64.Type
   ),
   cols_select_1 = Table.SelectColumns(col_index, {"Индекс", "Value"}),
   col_expand_table_1 = Table.ExpandTableColumn(
      cols_select_1,
      "Value",
      {"Name", "Value"},
      {"Name", "Value.1"}
   ),
   col_cell_width = Table.AddColumn(
      col_expand_table_1,
      "Cell Width",
      each try
         [Value.1]
            {[Name = "tcPr"]}
            [Value]
            {[Name = "tcW"]}
            [Attributes]
            {[Name = "w"]}
            [Value]
      otherwise
         null
   ),
   col_number_of_cells = Table.AddColumn(
      col_cell_width,
      "Number of Cells",
      each try
         Number.From(
            [Value.1]
               {[Name = "tcPr"]}
               [Value]
               {[Name = "gridSpan"]}
               [Attributes]
               {0}
               [Value]
         )
      otherwise
         1
   ),
   col_cell_values = Table.AddColumn(
      col_number_of_cells,
      "Cell Values",
      each fn_get_values([Value.1])
   ),
   cols_select_2 = Table.SelectColumns(
      col_cell_values,
      {"Индекс", "Number of Cells", "Cell Values"}
   ),
   col_replace_values_1 = Table.ReplaceValue(
      cols_select_2,
      each [Cell Values],
      each List.Repeat({[Cell Values]}, [Number of Cells]),
      Replacer.ReplaceValue,
      {"Cell Values"}
   ),
   col_expand_list = Table.ExpandListColumn(
      col_replace_values_1,
      "Cell Values"
   ),
   cols_select_3 = Table.RemoveColumns(
      col_expand_list,
      {"Number of Cells"}
   ),
   rows_group = Table.Group(
      cols_select_3,
      {"Индекс"},
      {
         {
            "Таблица",
            each _,
            type table [Индекс = number, Cell Values = text]
         }
      }
   ),
   col_replace_values_2 = Table.ReplaceValue(
      rows_group,
      each [Таблица],
      each Table.AddIndexColumn([Таблица], "idx", 0),
      Replacer.ReplaceValue,
      {"Таблица"}
   ),
   col_expand_table_2 = Table.ExpandTableColumn(
      col_replace_values_2,
      "Таблица",
      {"Cell Values", "idx"},
      {"Cell Values", "idx"}
   ),
   table_pivot = Table.Pivot(
      Table.TransformColumnTypes(
         col_expand_table_2,
         {{"idx", type text}},
         "ru-RU"
      ),
      List.Distinct(
         Table.TransformColumnTypes(
            col_expand_table_2,
            {{"idx", type text}},
            "ru-RU"
         )[idx]
      ),
      "idx",
      "Cell Values"
   ),
   cols_select_4 = Table.RemoveColumns(table_pivot, {"Индекс"}),
   table_transpose_1 = Table.Transpose(cols_select_4),
   rows_select_5 = Table.SelectRows(
      table_transpose_1,
      each [Column1] <> null and [Column1] <> ""
   ),
   table_transpose_2 = Table.Transpose(rows_select_5),
   table_promote_headers = Table.PromoteHeaders(
      table_transpose_2,
      [PromoteAllScalars = true]
   )
in
   table_promote_headers

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	Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.