Power Query. Импорт таблиц PDF

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

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

• YouTube
• VK

Ссылки:

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

Описание

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

Решение

Чтобы подключиться к одному файлу PDF сначала нужно получить его содержимое с помощью File.Contents, а потом извлечь таблицы функцией Pdf.Tables.

Чтобы получить таблицы из всех PDF файлов в папке нужно создать пользовательскую функцию для обработки одного файла и применить ее ко всем файлам в папке.

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

• File.Contents
• Pdf.Tables
• Table.AddColumn
• List.PositionOf
• Table.SelectRows
• Table.ReplaceValue
• Table.Skip
• Table.SelectColumns
• Table.PromoteHeaders
• Table.ExpandTableColumn
• Table.TransformColumnTypes
• Int64.Type
• Table.RemoveRowsWithErrors
• Folder.Files

Код

Код для импорта таблиц из одного файла:

let
   file_path = Excel.CurrentWorkbook(){[Name = "file_path"]}[Content]{0}[Column1],
   connect_pdf = File.Contents(file_path),
   import_pdf = Pdf.Tables(connect_pdf),
   col_add = Table.AddColumn(
      import_pdf,
      "Start of Table",
      each List.PositionOf([Data][Column1], "Дата")
   ),
   rows_select = Table.SelectRows(col_add, each ([Start of Table] <> - 1)),
   col_replace_value_1 = Table.ReplaceValue(
      rows_select,
      each [Data],
      each Table.Skip([Data], [Start of Table]),
      Replacer.ReplaceValue,
      {"Data"}
   ),
   cols_select = Table.SelectColumns(col_replace_value_1, {"Data"}),
   col_replace_value_2 = Table.ReplaceValue(
      cols_select,
      each [Data],
      each Table.PromoteHeaders([Data]),
      Replacer.ReplaceValue,
      {"Data"}
   ),
   col_expand = Table.ExpandTableColumn(
      col_replace_value_2,
      "Data",
      {
         "Дата",
         "№ док.",
         "ВО",
         "Банк контрагента",
         "Контрагент",
         "Счет контрагента",
         "Дебет",
         "Кредит",
         "Назначение платежа"
      },
      {
         "Дата",
         "№ док.",
         "ВО",
         "Банк контрагента",
         "Контрагент",
         "Счет контрагента",
         "Дебет",
         "Кредит",
         "Назначение платежа"
      }
   ),
   col_type = Table.TransformColumnTypes(
      col_expand,
      {{"Дата", type date}, {"№ док.", Int64.Type}, {"ВО", Int64.Type}}
   ),
   col_remove_errors = Table.RemoveRowsWithErrors(col_type, {"Дата"}),
   col_remove_nulls = Table.SelectRows(
      col_remove_errors,
      each [Дата] <> null and [Дата] <> ""
   ),
   cols_types = Table.TransformColumnTypes(
      col_remove_nulls,
      {{"Дебет", type number}, {"Кредит", type number}},
      "en-001"
   )
in
   cols_types

Код пользовательской функции по названием fn_get_tables:

(file_binary) =>
   let
      /*file_path = Excel.CurrentWorkbook(){[Name="file_path"]}[Content]{0}[Column1],
    connect_pdf = File.Contents(file_path),*/
      import_pdf = Pdf.Tables(file_binary),
      col_add = Table.AddColumn(
         import_pdf,
         "Start of Table",
         each List.PositionOf([Data][Column1], "Дата")
      ),
      rows_select = Table.SelectRows(col_add, each ([Start of Table] <> - 1)),
      col_replace_value_1 = Table.ReplaceValue(
         rows_select,
         each [Data],
         each Table.Skip([Data], [Start of Table]),
         Replacer.ReplaceValue,
         {"Data"}
      ),
      cols_select = Table.SelectColumns(col_replace_value_1, {"Data"}),
      col_replace_value_2 = Table.ReplaceValue(
         cols_select,
         each [Data],
         each Table.PromoteHeaders([Data]),
         Replacer.ReplaceValue,
         {"Data"}
      ),
      col_expand = Table.ExpandTableColumn(
         col_replace_value_2,
         "Data",
         {
            "Дата",
            "№ док.",
            "ВО",
            "Банк контрагента",
            "Контрагент",
            "Счет контрагента",
            "Дебет",
            "Кредит",
            "Назначение платежа"
         },
         {
            "Дата",
            "№ док.",
            "ВО",
            "Банк контрагента",
            "Контрагент",
            "Счет контрагента",
            "Дебет",
            "Кредит",
            "Назначение платежа"
         }
      ),
      col_type = Table.TransformColumnTypes(
         col_expand,
         {{"Дата", type date}, {"№ док.", Int64.Type}, {"ВО", Int64.Type}}
      ),
      col_remove_errors = Table.RemoveRowsWithErrors(col_type, {"Дата"}),
      col_remove_nulls = Table.SelectRows(
         col_remove_errors,
         each [Дата] <> null and [Дата] <> ""
      ),
      cols_types = Table.TransformColumnTypes(
         col_remove_nulls,
         {{"Дебет", type number}, {"Кредит", type number}},
         "en-001"
      )
   in
      cols_types

Код для обработки всех PDF файлов в папке:

let
   folder_path = Excel.CurrentWorkbook(){[Name = "folder_path"]}[Content]{0}[Column1],
   folder_files = Folder.Files(folder_path),
   rows_select = Table.SelectRows(folder_files, each ([Extension] = ".pdf")),
   table_add_col = Table.AddColumn(
      rows_select,
      "pdf_tables",
      each fn_get_tables([Content])
   ),
   cols_select = Table.SelectColumns(table_add_col, {"pdf_tables"}),
   col_expand = Table.ExpandTableColumn(
      cols_select,
      "pdf_tables",
      {
         "Дата",
         "№ док.",
         "ВО",
         "Банк контрагента",
         "Контрагент",
         "Счет контрагента",
         "Дебет",
         "Кредит",
         "Назначение платежа"
      },
      {
         "Дата",
         "№ док.",
         "ВО",
         "Банк контрагента",
         "Контрагент",
         "Счет контрагента",
         "Дебет",
         "Кредит",
         "Назначение платежа"
      }
   )
in
   col_expand

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