Ссылка на конкретную ячейку в Power Query
Если начинаете работать с данными в Power Query, то очень быстро привыкаете мыслить не ячейками и их адресами а-ля "морской бой" (как в Excel), а столбцами и целыми таблицами, т.к. именно ими и оперирует Power Query. Тем не менее, с завидной регулярностью могут возникать ситуации, когда при работе в Power Query нам нужно ссылаться именно на конкретные отдельные ячейки в наших данных. Реализовать такое, на самом деле, несложно - особенно, если вам не страшно копнуть чуть глубже интерфейса Power Query, в язык М, который работает у него "под капотом".
Рассмотрим в качестве примера следующий сценарий. Предположим, что мы собираем отчеты от руководителей филиалов нашей компании в общей папке. Внутри эти отчёты выглядят следующим образом:
Обратите внимание на следующие моменты:
- имена листов везде одинаковые (Лист1);
- название филиала (город) всегда расположено в ячейке А4;
- положение ячейки с именем руководителя филиала - плавающее и зависит от количества строк данных в каждом файле
На выходе необходимо собрать данные из всех файлов и свести их в одну таблицу, где кроме данных о продажах, будут ещё и столбцы с городом и именем руководителя:
Давайте разберём весь процесс пошагово.
Шаг 1. Собираем файлы из папки
Первичная сборка данных из всех отчётов выполняется совершенно стандартно с помощью команды Данные - Получить данные - Из файла - Из папки (Data - Get data - From file - From folder). Затем в появившемся окне нужно выбрать Лист1, который мы хотим извлечь из каждого файла и нажать кнопку Преобразовать данные (Transform data):
Затем нажимаем кнопку с двойными стрелками Объединить файлы в заголовке столбца Content, чтобы извлечь их содержимое, выбираем нужный нам лист (Лист1) и жмём на ОК:
Как обычно в таких случаях, Power Query действует по следующему алгоритму:
- Берёт первый файл из папки в качестве примера и создаёт параметр.
- Создаёт автоматический запрос Преобразовать пример файла (Transform sample file), где извлекает данные из файла-примера.
- Создаёт функцию и применяет её к каждому файлу на шаге Вызывать настраиваемую функцию (Invoke custom function), формируя новый столбец с результатами работы этой функции в виде вложенных таблиц.
- Удаляет все лишние столбцы и разворачивает содержимое вложенных таблиц, соединяя данные из всех файлов в единое целое.
Глядя на получившийся результат, хорошо видно, что он пока далёк от идеала - полезные данные перемешались с мусором, шапки задублировались, итоги остались, а самое главное - нет нужных нам столбцов с названием филиала и именем его руководителя.
Исправить всё это проще всего в запросе Преобразовать пример файла - если мы сделаем это в нём, то внесённые изменения затем автоматически попадут в функцию и повлияют на извлечение данных из каждого файла далее.
Во-первых, удаляем шаг Измененный тип (Changed type) и шаг повышения заголовков - они тут совершенно ни к чему.
Во-вторых, к получившемуся исходному виду данных нам в будущем придётся обращаться повторно, так что удобнее будет создать новый шаг, нажав на кнопку fx в строке формул и переименовать его (клавишей F2 или правой кнопкой мыши) любым подходящим именем - например База.
После этого можно удалить лишние "мусорные" строки - 5 сверху и 3 снизу, используя команды Главная - Удалить строки - Удалить верхние/нижние строки (Home - Remove rows - Remove top/bottom rows). Первую строку нужно будет поднять в шапку там же - кнопкой Использовать первую строку в качестве заголовков (Use first row as headers). В итоге наша табличка должна принять следующий цивильный вид:
Шаг 2. Извлекаем фиксированный город
Дальше самое интересное. Чтобы добавить к нашим данным столбец с названием города, нам нужно суметь обратиться к ячейке А4 и извлечь её содержимое. Для этого нам нужно понять, как Power Query формирует ссылку на конкретную ячейку, например для ячейки с городом это будет:
=База[Column1]{3}
Она состоит из трёх блоков:
- таблица, т.е. имя переменной (шага), где хранится исходная таблица - в нашем случае это шаг База
- имя столбца в квадратных скобках - у нас это [Column1]
- номер строки в фигурных скобках (считая с нуля) - у нас город лежит в 4-й строке, так что это будет 3
Теперь можно добавить новый вычисляемый столбец командой Добавление столбца - Настраиваемый столбец (Add column - Custom column) и ввести туда эту формулу. После нажатия на ОК мы увидим колонку с названием филиала:
Шаг 3. Извлекаем плавающего руководителя
Аналогичным образом добавим настраиваемый столбец и для извлечения имени руководителя филиала. Но тут надо вспомнить, что строка с именем руководителя у нас плавающая, так что её номер в фигурных скобках нужно не прописывать жёсткой константой, а вычислять для каждого файла. В этом нам может помочь функция языка М List.PositionOf - аналог экселевской ПОИСКПОЗ (MATCH). С её помощью мы можем найти позицию словосочетания "руководитель филиала" в первом столбце наших данных - это и будет искомый номер строки для последующего извлечения ФИО руководителя из третьей колонки.:
У функции List.PositionOf задаём следующие аргументы:
- исходный список, где мы ищем совпадение - в нашем случае это первый столбец в наших данных, т.е. База[Column1]
- искомое значение, т.е. "руководитель филиала";
- порядковый номер вхождения, которое нам нужно (считая с нуля) - ищем первое вхождение, поэтому ставим 0;
- нужно ли учитывать регистр при поиске - не нужно, поэтому используем Comparer.OrdinalIgnoreCase
Теперь номер плавающей строки, откуда мы берём ФИО руководителя будет вычисляться для каждого файла.
Останется лишь нажать на ОК в окне создания настраиваемого столбца и можно возвращаться в наш главный запрос, чтобы полюбоваться на результат:
Ссылки по теме
- РегистроНЕчувствительность в Power Query
- Сборка разноформатных таблиц в Power Query
- Сборка таблиц с разными шапками из нескольких книг
n = List.PositionOf( Record.ToList( Источник{1} ), "Вася" )После этого можно удалить все столбцы правее n командой: