Перейти к содержимому

Сохранение контекста в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Часто важное значение для анализа имеет контекстная информация, которая сохраняется вне таблиц. Объединение таблиц без контекста часто приводит к лишенным смысла результатам. Здесь будет показано, каким образом можно сохранять контекст таблицы и как объединять отдельные таблицы с их контекстной информацией.

Сохранение контекста в именах файлов и листах

Здесь рассматривается сценарий, когда контекстная информация доступна в виде наименования листа или имени файла, но не в виде столбца в таблице. Цель – включить эти данные в виде столбца для сохранения контекста. Допустим записи о товарах в категориях Bikes и Accessories расположены в двух отдельных книгах: C05E01 — Bikes.xlsx и C05E01 — Accessories.xlsx. Загрузите эти файлы на диск из приложенного архива.

Откройте новую книгу Excel. Загрузите файлы Bikes и Accessories, как отдельные запросы, выбрав вкладку Sheet1 в окне Навигатор. Запросы получат названия Sheet1 и Sheet1 (2). Информация, относящаяся к родительской категории, недоступна в виде столбца.

Рис. 1. При импорте таблиц Bikes и Accessories утрачивается контекст, связанный с именами файлов; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате архива (внутри несколько файлов Excel без поддержки макросов)

Переименуйте запросы в Bikes и Accessories. Чтобы увидеть, чему соответствует Sheet1 посмотрите формулу на шаге Источник:

Рис. 2. Переименуйте запрос в соответствии с именем файла

Выберите запрос Bikes. Пройдите Главная –> Добавить запросы –> Добавить запросы в новый. В окне Добавление выберите таблицу Bikes в качестве первой таблицы и Accessories – в качестве второй. Кликните Ok. Новый запрос Добавить1 содержит данные, относящиеся к товарам из таблиц Bikes и Accessories, но родительская категория утеряна. Переименование исходных запросов Sheet1 и Sheet1 (2) в Bikes и Accessories не является достаточным для сохранения контекста.

Выберите запрос Bikes. Пройдите Добавление столбца –> Настраиваемый столбец. В окне Настраиваемый столбец в поле Имя нового столбца введите Parent Category, а в окне Пользовательская формула столбца введите формулу:

Кликните Ok. Повторите процедуру добавления столбца для запроса Accessories. Теперь запрос Добавить1 содержит столбец Parent Category, включающий правильный контекст для строк из Bikes и Accessories.

Обработка контекста, состоящего из имен файлов и листов

При объединении нескольких файлов из папки, если имена файлов содержат контекст, можно найти его в столбце Source.Name и пользовательский столбец добавлять не нужно. При работе с контекстом, находящимся в именах листов, имеется два способа для сохранения этой информации. Если число листов невелико и их имена не будут меняться, то можно импортировать каждый лист отдельно и сохранить контекст, воспользовавшись вышеописанной методикой, основанной на применении настраиваемых столбцов. Или можно сделать следующее.

Загрузить файл C05E01 — Bikes & Accessories.xlsx. Откройте пустую книгу. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C05E01 — Bikes & Accessories.xlsx и кликните Импорт. В окне Навигатор выделите книгу (а не один или несколько листов) и кликните Преобразовать данные. В редакторе Power Query выделите столбцы Name и Data, кликните правой кнопкой мыши и выберите Удалить другие столбцы. Извлеките информацию из столбца Data. Благодаря столбцу Name сохранен контекст родительской категории.

Предварительное добавление для сохранения заголовков

В реальной жизни часто возникают ситуации, в которых контекстная информация сохраняется вне таблиц, а не в виде имен файлов или листов. Рассмотрим метод сохранения заголовков, которые доступны в источнике данных перед строками таблицы. Он называется методом предварительного добавления, поскольку применяется перед этапом добавления нескольких файлов. Ниже показаны шаги преобразования, необходимые для извлечения информации из заголовка на листе Excel и добавления ее в качестве нового столбца. Данный метод также может применяться к текстовым и CSV-файлам в папке.

Рис. 3. Шаги метода предварительного добавления

Загрузите файл C05E02 — Bikes.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C05E02 — Bikes.xlsx и щелкните Импорт. Выберите лист Sheet1 в окне Навигатор. Щелкните Преобразовать данные. Переименуйте запрос в Bikes. Удалите шаги Повышенные заголовки и Измененный тип.

Последнее действие не является обязательным, но на данном этапе применять автоматическую команду Измененный тип бессмысленно, поскольку Power Query не может правильно распознать типы столбцов таблицы товаров, первые строки которых содержат заголовок и пустые строки.

Пройдите Главная –> Удалить строки –> Удалить пустые строки. Этот шаг важен, поскольку облегчает последующий импорт строк таблицы товаров, после сохранения родительской категории. Правой кнопкой мыши щелкните на запросе Bikes, и выберите Ссылка. Выберите запрос Bikes (2). Обратите внимание, что первая ячейка в первой строке содержит значение Bikes. Щелкните правой кнопкой мыши на этой ячейке и выберите Детализация. Это приведет к отображению слова Bikes на панели предварительного просмотра.

Рис. 4. Редактор PQ после применения команды Детализация

Применение детализации к ячейке таблицы (объяснение с помощью M-формул)

В строке формул появится следующая строка:

Данная формула языка M извлекает значение Bikes из таблицы. Здесь Источник – имя таблицы, предоставленной на предыдущем шаге. Также можно заметить, что Источник – это первый шаг на панели Примененные шаги. Формула Источник{0} возвращает первую строку в таблице Источник (а Источник{1} возвращает вторую строку и т.д.). Индекс изменяется от 0 до n — 1 для доступа к первой или к n-й строкам. В большинстве сценариев можно не углубляться в формулы М, а применять пользовательский интерфейс и детализацию для доступа к отдельным ячейкам.

В Power Query строки представлены в виде записей. Запись является списком пар ключ/значение, где ключ — имя столбца, а значение — соответствующее значение в строке. Получить доступ к значению по заданному ключу в записи можно с помощью формулы запись [ключ], где запись — имя записи, а ключ — имя ключа. Следовательно, формула Источник{0}[Column1] возвращает значение в первой строке таблицы Источник в столбце Column1.

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

Объединение в запросе результатов детализации

Теперь, когда известно, каким образом применяется детализация для извлечения значения из ячейки, удалите запрос Bikes (2) и выберите запрос Bikes. Правой кнопкой мыши щелкните на ячейке, которая содержит значение Bikes, и в контекстном меню выберите команду Детализация. В строке формул вы увидите:

На панели просмотра появилось значение «Bikes». На панели Примененные шаги этот шаг назван Column1. Для придания значения этому шагу переименуйте его в Title. Находясь на шаге Title, кликните fx и замените формулу:

на

Нажмите Enter. Это позволяет пропустить шаг Title и вернуться к предыдущему шагу. Теперь на панели предварительного просмотра вместо слова Bikes отображается таблица. Но шаг Title остался в разделе Примененные шаги. Для удаления первой строки пройдите Главная –> Удалить строки –> Удаление верхних строк –> 1. Кликните Ok. Повысьте имена столбцов. Для этого пройдите Преобразование –> Использовать первые строки в качестве заголовков.

Создайте настраиваемый столбец с Bikes как родительской категорией. Пройдите Добавление столбца –> Настраиваемый столбец. Настройте окно:

Рис. 5. Настраиваемый столбец

В таблицу добавлен новый столбец Bikes. Удалось динамически извлечь слово Bikes из ячейки, которая использовалась как заголовок рабочего листа, и выполнить шаги, показанные на рис. 3, чтобы сохранить его в таблице. Эта чрезвычайно полезная методика.

Возможность разделить преобразования на два потока, а затем объединить в один является универсальной методикой, применяемой при работе в Power Query. По мере знакомства с языком M подобные манипуляции можно применять непосредственно в расширенном редакторе вместо формирования настраиваемых шагов, как это выполнено выше.

Сохранение заголовков из папки

Описанный выше метод обобщим на импорт всех книг из папки. Загрузите в папку Products файлы C05E03-01.xlsx, C05E03-02.xlsx, C05E03-03.xlsx, C05E03-04.xlsx. Изучите файлы: каждый из них содержит таблицу товаров с отличающимся названием, поэтому единственный способ сохранить родительские категории продуктов – это извлечь заголовки.

Откройте новую книгу. Пройдите по меню: Данные –> Получить данные –> Из файла –> Из папки. В окне Обзор выберите папку Products, кликните Ok, хотя ни один файл не выбран. В следующем окне показаны четыре файла, имеющиеся в папке Products. Пройдите Объединить –> Объединить и преобразовать. В окне Объединить файлы выделите лист Sheet1 и щелкните Ok.

В окне редактора Power Query заметьте, что в объединенной таблице Products отсутствует контекст родительской категории. Выберите запрос Преобразовать пример файла и переименуйте его в Products Sample. Удалите шаг Повышенные заголовки. Пройдите Главная –> Удалить строки –> Удалить пустые строки (что соответствует первому шагу на рис. 3). Правой кнопкой мыши щелкните на ячейке со значением Bikes, и выберите команду Детализация. В строке формул вы увидите:

На панели Примененные шаги этот шаг назван Column1. Переименуйте шаг в Title. Щелкните на fx и замените формулу:

на:

Нажмите Enter. Пройдите Главная –> Удалить строки –> Удаление верхних строк –> 1. Нажмите Ok. Пройдите Преобразование –> Использовать первую строку в качестве заголовков. Пройдите Добавление столбца –> Настраиваемый столбец. В окне Настраиваемый столбец в поле Имя нового столбца введите Parent Category, а в поле формула введите:

Кликните Ok. Выделите запрос Products Sample удалите шаг Измененный тип. Этот шаг в дальнейшем может привести к ошибкам в столбце Size. Типы столбцов лучше явно изменять позже.

Выберите запрос Products. Удалите последний шаг Измененный тип. Выберите элемент управления фильтра для заголовка столбца Parent Category, убедитесь, что в столбце представлены все четыре категории товаров.

Сохранение заголовков при постдобавлении контекста

В предыдущем разделе мы воспользовались методом предварительного добавления для динамического извлечения значения заголовка и присоединения его в качестве настраиваемого столбца перед этапом добавления. Этот подход эффективен, если имеется ограниченное число таблиц для добавления или если он применяется для объединения нескольких таблиц из папки. Иногда легче извлекать заголовки из добавленной (объединенной) таблицы. Этот метод называют постдобавлением, поскольку он применяется после шага добавления. Метод постдобавления удобен, если добавляются несколько листов из книги или если источник данных включает несколько таблиц в одном текстовом файле, причем каждая таблица отделена от предыдущей таблицы строкой заголовка.

Загрузите файл C05E04 — Products.xlsx. Откройте его. Обратите внимание, что товары компании разделены по родительским категориям на четыре листа, а заголовки находятся в ячейках над таблицами. Откройте новую книгу в Excel, пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C05E04 — Products.xlsx и кликните Импорт. В окне Навигатор выделите папку C05E04 — Products.xlsx и кликните Преобразовать данные. В окне отобразится таблица с четырьмя листами. Кликните правой кнопкой мыши на столбце Data и выберите Удалить другие столбцы. Разверните столбец Data. В открывшемся окне оставьте выбранными все столбцы и снимите галочку Использовать исходное имя столбца как префикс. Нажмите Ok.

Рис. 6. Импорт объединил четыре листа

Для извлечения заголовков и передачи их в столбец Parent Category необходимо выполнить последовательность преобразований:

Рис. 7. Шаги метода постдобавления

Пройдите Главная –> Удалить строки –> Удалить пустые строки. Затем Добавление столбца –> Условный столбец. Ваша задача – создать условный столбец, который поможет идентифицировать строки с заголовками. Существуют два подхода к определению названий:

  1. При интуитивном подходе вы можете определить, соответствует ли первый столбец названию Bikes, Accessories, Components или Clothing, и установить новый столбец как 1, если это так, или как 0 – в противном случае. Подобный подход не рекомендован, поскольку для определения названий применяет жестко закодированные значения. В будущем могут появиться новые родительские категории в качестве заголовков, но на этом этапе нельзя предсказать их значения. Таким образом, каждый раз при получении нового листа с другой родительской категорией нужно будет изменять запрос, чтобы затем его подключить.
  2. Более надежный подход заключается в том, чтобы идентифицировать заголовки, как строки со значениями null во всех столбцах, кроме Column1. В результате можно сформировать запрос однократно, и нет необходимости изменять его всякий раз, когда к рабочим листам добавляются новые родительские категории.

Реализуем надежный подход. Настройте окно Добавление условного столбца:

Рис. 8. Настройка окна Добавление условного столбца для динамической идентификации заголовков

Нажмите Ok. Появился условный столбец IsTitle, в котором строке заголовка будет соответствовать единица, а прочим строкам – ноль. Добавим еще один условный столбец. Пройдите Добавление столбца –> Условный столбец:

Рис. 9. Настройка второго условного столбца

Нажмите Ok. Используем трансформацию заполнения. Правой кнопкой мыши щелкните на заголовке столбца Title, выберите Заполнить –> Вниз. Этот шаг позволяет сохранять «правильную» родительскую категорию в каждой строке. Теперь можно отфильтровать строки заголовков с помощью столбца IsTitle. Щелкните на элементе управления фильтра в заголовке столбца IsTitle. Отмените выбор значения 1. Удалите столбец IsTitle. Пройдите Преобразование –> Использовать первую строку в качестве заголовков. Переименуйте столбец Bikes в Parent Category. Отфильтруйте заголовки столбцов второго и последующих листов, например, сняв фильтр со значения Name в столбце Name.

Можно повысить надежность кода на шаге Переименнованные столбцы, заменив:

на:

Power Query вместо явного переименования столбца Bikes переименует последний столбец. Это важно, потому что в реальной жизни данные не остаются неизменными. Исходный запрос не будет обновлен, если таблица Bikes больше не является первым листом в книге.

Загрузите объединенную таблицу на лист Excel.

Использование подсказок контекста

В предыдущем сценарии мы идентифицировали заголовки, как существующие в строках, где первый столбец не пустой, а все остальные столбцы – пустые. Но как быть, если такое предположение сделать нельзя? Имеется способ идентификации заголовков по их близости к ячейкам привязки. Эти ячейки служат подсказками, помогающими находить нужный контекст.

Загрузите файл C05E05 — Products.xlsx. Откройте его. Родительская категория находится внутри небольшой таблицы.

Рис. 10. Ваша задача – извлечь родительскую категорию из верхней таблицы

Зададим значение родительской категории, используя расположение текста Parent Category.

Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C05E05 — Products.xlsx и кликните Импорт. В окне Навигатор выберите папку C05E05 — Products.xlsx и кликните Преобразовать данные. Правой кнопкой мыши щелкните на заголовок столбца Data и выберите Удалить другие столбцы.

В этом сценарии предположим, что после удаления пустых строк значение родительской категории в каждом рабочем листе находится в ячейке на пересечении второй строки и первого столбца. Воспользуемся столбцом индекса и применим его к каждой таблице в столбце Data. Пройдите Добавление столбца –> Столбец индекса. Затем скопируйте формулу из строки формул:

Теперь можно удалить шаг Добавлен индекс. Вам просто нужна формула, чтобы подготовить почву для следующих шагов. Пройдите Добавление столбца –> Настраиваемый столбец. В окне Настраиваемый столбец в поле Имя нового столбца введите Таблица с индексом. В области Настраиваемая формула столбца вставьте скопированную формулу. Модифицируйте её: удалите второй знак равенства (он добавляется по умолчанию); замените строку #"Другие удаленные столбцы" на [Data]. Получим:

Рис. 11. Окно Настраиваемый столбец

Щелкните Ok. Теперь на панели предварительного просмотра есть два столбца Data с исходными объектами таблицы для каждого импортированного рабочего листа и столбец Таблица с индексом с теми же объектами таблицы и добавленным столбцом индекса. Удалите столбец Data. Разверните столбец Таблица с индексом, щелкая мышью на значке развертывания в правой части заголовка. Снимите флажок Использовать исходное имя столбца как префикс, нажмите Ok. Просмотрите результат. Родительская категория доступна в строках, где столбец индекса имеет значение, равное единице. Заголовки Parent Category и Report Date находятся в строках, где столбец индекса имеет нулевое значение.

Для того чтобы отметить строки со значениями Parent Category пройдите Добавление столбца –> Условный столбец. Настройте окно:

Рис. 12. Окно Добавление условного столбца

Кликните правой кнопкой мыши на заголовке столбца Parent Category, пройдите Заполнить –> Вниз. Кликните на фильтр в заголовке столбца Индекс, снимите галочки со значений 0, 1 и 2. Теперь столбец Индекс можно удалить.

Пройдите Преобразование –> Использовать первую строку в качестве заголовков. Переименуйте столбец Bikes в Parent Category. Замените формулу

на более надежную

Отфильтруйте заголовки второго и последующих листов, содержащие значение Name в столбце Name. Загрузите запрос в таблицу на лист Excel.

Идентификация контекста с учетом близости ячейки

Изменим контекст. Загрузите файл C05E06 — Products.xlsx. Откройте его. Можно видеть, что расположение родительской категории изменяется от листа к листу:

Рис. 13. Расположение имени родительской категории меняется от листа к листу

Для данного сценария предположим, что значение родительской категории можно найти ниже ячейки со значением Parent Category в первом столбце. Кроме того, на листах присутствует нерелевантное содержимое (выше строк с именем родительской категории), которое следует исключить. Методика основана на применении функции M List.PositionOf. Эта функция получает список в качестве входных аргументов и значение для поиска своей позиции, а возвращает индекс (начиная с нуля) позиции значения в списке.

Повторите шаги, описанные в примере выше до получения двух столбцов: Data и Таблица с индексом. Кратко повторю. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C05E06 — Products.xlsx и кликните Импорт. В окне Навигатор выберите папку C05E06 — Products.xlsx и кликните Преобразовать данные. Правой кнопкой мыши щелкните на заголовок столбца Data и выберите Удалить другие столбцы. Пройдите Добавление столбца –> Столбец индекса. Скопируйте формулу:

Удалите шаг Добавлен индекс. Пройдите Добавление столбца –> Настраиваемый столбец. В окне Настраиваемый столбец в поле Имя нового столбца введите Таблица с индексом. В области Настраиваемая формула столбца вставьте скопированную формулу. Модифицируйте её: удалите второй знак равенства; замените строку #"Другие удаленные столбцы" на [Data]. Щелкните Ok. Вы получили таблицу с двумя столбцами: Data и Таблица с индексом.

Пройдите Добавление столбца –> Настраиваемый столбец. В поле Имя нового столбца введите Индекс строки родительской категории. Введите формулу:

Эта формула применит функцию List.PositionOf для столбца Column1, который имеет вид списка и предоставляется в качестве первого аргумента [Data] [Column1]. Второй аргумент, «Parent Category», — это значение, которое желательно найти в списке для возврата его позиции в списке. В столбце Индекс строки родительской категории появятся различные числа.

Рис. 14. Индекс строки родительской категории

Каждое число представляет местоположение ячейки Parent Category в индексе, начинающемся с нуля.

Создадим еще один настраиваемый столбец. На этот раз детализируем каждый объект таблицы в столбце Data, применяя индекс, найденный на предыдущем шаге, для получения фактических значений родительской категории. Напомним, что значение родительской категории находится ниже ячейки со словами Parent Category. Пройдите Добавление столбца –> Настраиваемый столбец. В поле Имя нового столбца введите Parent Category. Введите формулу

Выше мы имели дело с более простой версией этой формулы:

Здесь синтаксис аналогичный. Сослаться на одну ячейку в таблице можно, написав следующее выражение:

Напомним, что фигурные скобки служат для доступа к строке в таблице с использованием индекса, начинающегося с нуля. Квадратные скобки обеспечивают доступ к имени столбца. Родительская категория сохранена в столбце Parent Category.

Рис. 15. Родительская категория

Определим индекс строки заголовков главной таблицы (товаров). На этот раз выполняется поиск значения Name, а не Parent Category. Пройдите Добавление столбца –> Настраиваемый столбец. В поле Имя нового столбца введите Индекс строки названия. Введите формулу:

Удалите столбцы Индекс строки родительской категории и Data. Разверните столбец Таблица с индексом, снимите флажок Использовать исходное имя столбца как префикс, щелкните Ok.

На этом этапе в результатах сохранена родительская категория. Следующая задача – использовать подсказки, которые имеются в столбцах Индекс и Индекс строки названия. Все строки, индекс которых меньше индекса строки названия, должны быть отфильтрованы, поскольку они не относятся к таблице товаров. Для этого создадим условный столбец, а затем отфильтруем по нему нерелевантные строки.

Пройдите Добавление столбца –> Условный столбец:

Рис. 16. Условный столбец

Нажмите Ok. Отфильтруйте данные в столбце Пользовательский, оставив галочку только напротив 1. Удалите столбцы Индекс, Индекс строки названия и Пользовательский. Выполните следующие уже известные шаги. Пройдите Преобразование –> Использовать первую строку в качестве заголовков. Переименуйте столбец Bikes в Parent Category. Замените формулу

на более надежную

Отфильтруйте заголовки второй и последующих таблиц, сняв галочку Name в столбце Name. Загрузите запрос в таблицу на лист Excel.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *