Главная Power Query Power Query. Импорт Word
Power QueryPower Query. Разное

Power Query. Импорт Word

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

Ссылки:

Описание

В этом уроке вы узнаете как импортировать таблицы из 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 разное

Номер урокаУрокОписание
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 Продвинутый №17. Нюансы консолидации из разных книг/листов Excel и List PositionOf

Power Query Базовый №13. Ссылка на другую строку

Power Query Базовый №23. Поиск ключевых слов

Power Query Продвинутый №6. Объект List 2

Power Query. Фильтрация списком

Power Query Базовый №5. Объединить все листы файла Excel по вертикали

Power Query Язык М №1. Простые выражения, let

Power Query Ошибки №19. Неверная разбивка на столбцы

Power Query. Знакомство с Power Query

Power Query Веб-запросы №12. Получить ссылки с веб-страницы (Извлечь все URL, Html.Table)