Ваш конспект

Извлечение отдельных ячеек в Power Query

Ключевые тезисы:
.### Основная идея

• В Power Query обычно работают со столбцами и таблицами целиком.
• Однако иногда возникает необходимость ссылаться на конкретные отдельные ячейки.
• Это реализуется через язык M с использованием составных ссылок.

Пример задачи

• Необходимо собрать отчёты из нескольких файлов Excel в одну таблицу.
• В каждом файле есть полезные данные и служебная информация в фиксированных и плавающих ячейках (например, название филиала и имя руководителя).
• Цель: создать итоговую таблицу с колонками: Дата, Артикул, Стоимость, Город, Руководитель.

Пошаговое решение

Сбор данных из папки

1. Используется стандартный путь: Данные → Получить данные → Из файла → Из папки.
2. После выбора папки нажимается «Преобразовать данные».
3. В столбце «Контент» разворачиваются данные (кнопка с двойными стрелками).
4. Выбирается нужный лист (в примере — «Лист1»).

Power Query автоматически создаёт:

• Параметр с примером файла.
• Запрос «Преобразовать пример файла».
• Функцию «Преобразовать файл», которая применяется ко всем исходным файлам.

Очистка и подготовка данных в тренировочном запросе

Работу удобнее вести не в основном запросе, а в автоматически созданном «Преобразовать пример файла», так как изменения в нём автоматически применятся ко всем файлам.

Шаги очистки:

• Удаление шага «Повышенные заголовки» (он лишний для данных без заголовков).
• Фиксация базового состояния: создание нового шага (например, с именем «База»), который просто ссылается на таблицу до преобразований.
• Удаление мусорных строк сверху и снизу таблицы с помощью команд «Удалить верхние строки» и «Удалить нижние строки».
• Поднятие первой строки данных в качестве заголовков («Использовать первую строку в качестве заголовков»).

Извлечение конкретных ячеек

Ссылка на ячейку в Power Query (язык M) формируется из трёх частей:

1. Таблица (или переменная, где она хранится).
2. Имя столбца в квадратных скобках [Column1].
3. Номер строки в фигурных скобках {3} (нумерация начинается с 0).

Пример извлечения города (ячейка A4):

• Город находится в столбце Column1, строке 3 (поскольку A4 — это 4-я строка, а нумерация с 0).
• Формула для настраиваемого столбца: = База[Column1]{3}

Пример извлечения руководителя (ячейка с плавающим положением):

• Руководитель находится в столбце Column3.
• Позицию строки нужно вычислять динамически.

Динамическое определение позиции строки

Для поиска строки с фразой «руководитель филиала» используется функция List.PositionOf.

Синтаксис и аргументы функции:

• List.PositionOf(список, искомое_значение, номер_вхождения, сравнение)
• Список: База[Column1] (столбец, в котором ищем).
• Искомое значение: "руководитель филиала".
• Номер вхождения: 0 (первое вхождение, нумерация с 0).
• Параметр сравнения: Comparer.OrdinalIgnoreCase (для регистронезависимого поиска).

Итоговая формула для столбца «Руководитель»:
= База[Column3]{ List.PositionOf(База[Column1], "руководитель филиала", 0, Comparer.OrdinalIgnoreCase) }

Итоговый результат

После настройки тренировочного запроса:

• Функция «Преобразовать файл» автоматически обновляется.
• В основном запросе «Отчёты» данные из всех файлов собираются в одну таблицу.
• К полезным данным добавляются колонки «Город» и «Руководитель», извлечённые из конкретных ячеек каждого файла.

Вывод: Используя составные ссылки и функции языка M (например, List.PositionOf), можно гибко извлекать данные из отдельных ячеек, даже с плавающим положением, что значительно расширяет возможности Power Query по обработке неструктурированных отчётов.