Ранее я перевел книгу Кен Пульс и Мигель Эскобар. Язык М для Power Query. А спустя некоторое время с удивлением обнаружил, что страничка книги самая посещаемая среди опубликованных за последние 5 лет. Механизм Power Query для Excel относительно новый, но весьма необычный. Это не чистая работа с данными в Excel, а инструмент импорта внешних данных и их предварительной обработки. Я постоянно извлекаю данные из Интернета, поэтому использую Power Query довольно часто. Гил Равив описывает многое из того, что есть у Кена Пульса, поэтому здесь я не повторяюсь. Больше внимания уделяю новым аспектам: языку М, анализу текстов и извлечению знания из текста. Книга содержим массу практически примеров, и будет очень полезна в освоении Power Query.
Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных. – СПб.: БХВ-Петербург, 2021. – 480 с.
Скачать заметку в формате Word или pdf, примеры в формате zip (внутри три файла Excel)
Купить бумажную или электронную книгу в издательстве, бумажную книгу в Ozon или Лабиринте
Содержание
Глава 1. Введение в Power Query
Глава 2. Основные проблемы, возникающие при подготовке данных
Извлечение гиперссылок из текста
Разделение данных на таблицы поиска и фактов в Power Query
Глава 3. Объединение данных, полученных из нескольких источников
Объединение нескольких таблиц в Power Query
Объединение большого числа таблиц в Power Query
Глава 4. Объединение несовпадающих таблиц в Power Query
Глава 6. Отмена свертывания столбцов таблицы
Глава 7. Дополнительные вопросы, связанные с применением преобразования сведения столбцов и отмены свертывания столбцов
Отмена свертывания столбцов таблицы с несколькими уровнями иерархии
Создание функции отмены свертывания столбцов
Преобразование сведения в столбец
Глава 9. Введение в язык формул M Power Query
Глава 10. От ловушек к робастным запросам
Глава 11. Основы текстовой аналитики
Глава 12. Расширенная текстовая аналитика: извлечение смысла
Показано, как использовать облачные службы Microsoft Azure Cognitive Services для анализа настроений, перевода с языка и извлечения ключевых фраз. Чтобы выполнить упражнения этой главы требуется учетная запись в Microsoft Azure.
Глава 13. Аналитика социальных сетей в Power Query
Глава посвящена анализу социальных сетей, в частности Facebook. Power Query включает соединитель Facebook, который позволяет подключаться к вашему профилю или любой общедоступной странице. Информация доступна для всех, и она поможет вам попрактиковаться в подготовке неструктурированных наборов данных, в обработке разнообразного набора таблиц и необработанных данных, взятых из разнообразных и обширных наборов.
Глава 14. Заключительный проект в Power Query: объединяем все вместе
Глава 2. Основные проблемы, возникающие при подготовке данных
Примеры в книге часто используют базу AdventureWorks, придуманную Microsoft для демонстрации реализации приложений в бизнес-сценариях. Для учебных целей базу данных AdventureWorks можно развернуть на сервере SQL на локальном ПК (подробнее см. Дик Куслейка. Визуализация данных при помощи дашбордов и отчетов в Excel, раздел Базы данных SOL Server). Вы также можете развернуть базу данных в облаке Azure (см. Quickstart: Create an Azure SQL Database single database).
Столбец из примеров
Загрузите файл C02E02.xlsx с сайта или из приложенного архива. Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. Выделите файл C02E02.xlsx и нажмите Импорт. В открывшемся окне Навигатор выберите таблицу Products и нажмите кнопку Преобразовать данные. Откроется окно редактора Power Query. Столбец Product Number содержит четыре кода, разделенных дефисом (например, в первой строке VE-C304-S-BE). Нужно извлечь третье значение, которое соответствует размеру товара.
Выделите столбец Product Number, пройдите по меню Добавление столбца –> Столбец из примеров –> Из выделения. Редактор Power Query переходит в новое состояние. В верхней части панели просмотра появляется сообщение: Введите примеры значений, чтобы создать столбец (рис. 1). В правой части панели в первую ячейку столбца Текст между разделителями введите S. Переименуйте столбец в Size. Нажмите Enter, Power Query заполнит столбец Size предложенными значениями, как показано на рис. 1. Нажмите комбинацию клавиш Ctrl+Enter или щелкните мышью на кнопке Ok для создания столбца. Загрузите данные в книгу Excel.
Рис. 1. Редактор Power Query в режиме Добавить столбец из примеров; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Если в окне Параметры запроса на строке Вставленный текст между… кликнуть на шестеренке, откроется окно Текст между разделителями, а верхней части можно увидеть код на языке М, соответствующий настройкам на рис. 2:
Рис. 2. Интерфейс и код на языке М для извлечения символа между разделителями
С полным перечнем функций, доступных в раскрывающемся меню Столбец из примеров, можно ознакомиться на сайте MS.
Объединение чисел в группы
Столбец Size содержит как буквенные, так и числовые значения. Игнорируем текстовые значения, а числовые значения разобьем на группы:
- ≥ 70 – X
- ≥ 60 – L
- ≥ 50 – M
- < 50 – S
Для извлечения числовых размеров используем обработку ошибок. Для этого кликните правой кнопкой мыши на столбце Size и выберите команду Создать дубликат столбца. Переименуйте новый столбец в Size Numbers. Кликните на нем правой кнопкой мыши и выберите Изменить тип –> Целое число. Вместо текстовых значения отразятся ошибки – Error. Кликните правой кнопкой мыши на столбце Size Numbers и выберите Удалить ошибки. Выделите столбец Size Numbers и пройдите по меню Добавление столбца –> Столбец из примеров –> Из выделенного. Проставьте для нескольких первых строк буквенные значения группы размеров:
Рис. 3. Столбец из примеров для групп размеров
Нажмите Ok или Ctrl+Enter. Отредактируйте код в соответствии с нашими диапазонами:
Рис. 4. Отредактированный код
Извлечение гиперссылок из текста
Загрузите файл C02E04.xlsx. Создайте новую книгу Excel (нажмите Ctrl+N). Импортируйте файл C02E04.xlsx в редактор Power Query. Для этого пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. В окне навигатора выберите лист Sheet1, нажмите Преобразовать данные. Откроется окно редактора Power Query. Видно, что столбец Message содержит текст произвольного формата. Создайте копию столбца Message, чтобы сохранить исходное сообщение без изменений. Выделите столбец и пройдите по меню Добавление столбца –> Создать дубликат столбца. Переименуйте новый столбец – Hyperlink.
Выделите столбец Hyperlink и пройдите по меню Преобразование –> Разделить столбец –> По разделителю. Настройте параметры разделения:
Рис. 5. Параметры разделения
Отлично! Но что если некоторые гиперссылки начинаются с "https://" или просто с "www."? На панели Примененные шаги выделите шаг Разделить столбец по разделителю. В строке формул отобразится:
|
1 2 3 4 |
= Table.SplitColumn(#"Переименованные столбцы", "Hyperlink", Splitter.SplitTextByEachDelimiter( {"http://"}, QuoteStyle.Csv, false), {"Hyperlink.1", "Hyperlink.2"} ) |
Для разделения столбца Hyperlink с помощью дополнительных разделителей измените имя функции с на Splitter.SplitTextByAnyDelimiter. Разделитель "https://" заключен в двойные кавычки в фигурных скобках. Фигурные скобки представляют собой списки на языке запросов M. Дополните список новыми разделителями. Формула примет вид:
|
1 2 3 4 |
= Table.SplitColumn(#"Переименованные столбцы", "Hyperlink", Splitter.SplitTextByAnyDelimiter( {"http://", "https://", "www."}, QuoteStyle.Csv, false), {"Hyperlink.1", "Hyperlink.2"} ) |
Обратите внимание, что в столбце Hyperlink.2, в строке 15 после гиперссылки есть дополнительный текст. Разделите столбец Hyperlink.2 по разделителю пробел, самый левый разделитель. Удалите столбец Hyperlink.2.2.
Проверьте результаты в столбце Hyperlink.2. В некоторых строках со значением null не были найдены наши разделители: "http://", "https://", "www." Однако текст в столбце Hyperlink.1 содержит гиперссылку на бесплатную электронную книгу с сайта: aka.ms/containersebook.
Рис. 6. Необработанные исключения
Если добавить в список разделителей еще и "aka.ms", этот фрагмент адреса исчезнет из поля после разделения (для предыдущих разделителей это было не страшно, так как www.microsoft.com и microsoft.com ведут на один и тот же сайт). Поэтому сначала мы добавим "aka.ms" в список разделителей, а затем для записей, начинающихся с символа / добавим в начало доменное имя aka.ms. Для этого перейдите к последнему шагу в панели Примененный шаги, и пройдите по меню Добавление столбца –> Условный столбец. Откроется диалог Добавление условного столбца.
Рис. 7. Настройки условного столбца
Поскольку пользовательский интерфейс ограничен, его иногда применяют для формирования кода, который затем будет отредактирован. Исходный код добавления условного столбца…
|
1 2 3 4 |
= Table.AddColumn(#"Удаленные столбцы", "Hyperlink", each if [Hyperlink.2.1] = null then null else if Text.StartsWith( [Hyperlink.2.1], "/") then [Hyperlink.2.1] else [Hyperlink.2.1] ) |
… изменим на
|
1 2 3 4 |
= Table.AddColumn(#"Удаленные столбцы", "Hyperlink", each if [Hyperlink.2.1] = null then null else if Text.StartsWith( [Hyperlink.2.1], "/") then "aka.ms" & [Hyperlink.2.1] else [Hyperlink.2.1] ) |
Нулевое условие здесь было добавлено, чтобы не допустить применения функции Text.StartsWith к значению null в поле [Hyperlink.2.1], что приведет к ошибке. Теперь столбец Hyperlink.2.1 можно удалить.
Продолжая проверять результаты в столбце Hyperlink, вы увидите, что строка 149 пуста. Но в столбце Message есть гиперссылка https://www.microsoftpressstore.com/Ignite. Проблема связана с тем, что на шаге Разделить столбец по разделителю в качестве разделителей использовались "https: //" и "www.". Поэтому результат разделения содержал три отдельных значения, но загружались только первые два. Чтобы устранить проблему, отредактируем код. Было:
|
1 2 3 4 |
= Table.SplitColumn(#"Переименованные столбцы", "Hyperlink", Splitter.SplitTextByAnyDelimiter( {"http://", "https://", "www.", "aka.ms"}, QuoteStyle.Csv, false), {"Hyperlink.1", "Hyperlink.2"} ) |
Стало:
|
1 2 3 4 |
= Table.SplitColumn(#"Переименованные столбцы", "Hyperlink", Splitter.SplitTextByAnyDelimiter( {"http://", "https://", "www.", "aka.ms"}, QuoteStyle.Csv, false), 3 ) |
Функция Table.SplitColumn принимает либо имена новых столбцов при разбиении, либо их число. Теперь, гиперссылки расположены в двух столбцах: Hyperlink.2 и Hyperlink.3.
Рис. 8. Разделение столбца Hyperlink на три
Объединим эти два столбца. Для этого выделите столбцы Hyperlink.2, Hyperlink.3 и пройдите по меню Преобразование –> Объединить столбцы. Появится предупреждение о том, что этот шаг будет вставлен между существующими шагами и может нарушить выполнение последующих шагов. Подтвердите действие. Сохраните заданные по умолчанию настройки:
Рис. 9. Параметры объединения столбцов
Удалите столбцы Hyperlink.2 и Hyperlink.3. Переименуйте столбец Сведено в Hyperlink.2. Каждый раз будет появляться предупреждение о вставке нового шага. Подтверждайте действия. Теперь вы можете продолжить двигаться по шагам, и увидите, что вставка нескольких шагов не нарушила сценарий.
Проблемы остались. В строке 149 гиперссылка закончилась не пробелом, а точкой. В результате она может не открыться. Или если за точкой вплотную (без пробела) следует лишний текст, разделение на следующем шаге с разделителем пробел не сработает. Следует убрать конечные знаки пунктуации во всех строках столбца Hyperlink.2. Выделите столбец и пройдите по меню Преобразование –> Формат –> Усечь. Эту команду можно также запустить, кликнув правой кнопкой мыши на столбце. По умолчанию команда Усечь удаляет пробелы в начале и конце текстовых значений. Можно ее использовать для очистки знаков пунктуации. Для этого отредактируйте код:
|
1 2 3 |
= Table.TransformColumns(#"Переименованные столбцы1",{ {"Hyperlink.2", Text.Trim, type text}} ) |
Функция языка M Text.Trim имеет два необязательных аргумента. В коде выше она просто удаляет пробелы в начале и в конце текста в каждой строке столбца Hyperlink.2. Но мы можем передать функции два аргумента: обрабатываемый текст, и список элементов, которые будут усекаться (помимо пробелов). Обрабатываемый текст передается символом подчеркивания. А символы усечения в виде списка. Новый код:
|
1 2 3 |
= Table.TransformColumns(#"Переименованные столбцы1",{ {"Hyperlink.2", each Text.Trim(_, {".",",",")"}), type text}} ) |
Следующая проблема в строке 174, где гиперссылка заканчивается новой строкой, за которой следует лишний текст. Для устранения этой проблемы в сценарии перейдите к шагу Разделить текст по разделителю1. В строке формул вы увидите код:
|
1 2 3 4 |
= Table.SplitColumn(#"Измененный тип1", "Hyperlink.2", Splitter.SplitTextByEachDelimiter( {" "}, QuoteStyle.Csv, false), {"Hyperlink.2.1", "Hyperlink.2.2"} ) |
Измените его на:
|
1 2 3 4 |
= Table.SplitColumn(#"Измененный тип1", "Hyperlink.2",Splitter.SplitTextByAnyDelimiter( {" ", "#(lf)"}, QuoteStyle.Csv, false), {"Hyperlink.2.1","Hyperlink.2.2"} ) |
Значение "#(lf)" описывает специальный символ перевода строки. Для использования нескольких разделителей вы заменили функцию Splitter.SplitTextByEachDelimiter на Splitter.SplitTextByAnyDelimiter.
Наконец, в части строк используются сокращенные ссылок на основе сервиса Bitly, что-то типа: bit.ly/28Cj0Yx. Добавьте в Разделить столбец по разделителю в список разделителей еще один:
|
1 2 3 4 |
= Table.SplitColumn(#"Переименованные столбцы", "Hyperlink", Splitter.SplitTextByAnyDelimiter ( {"http://", "https://", "www.", "aka.ms", "bit."}, QuoteStyle.Csv, false), 3 ) |
Добавьте в строку Условный столбец еще одно условие замены:
|
1 2 3 4 |
= if [Hyperlink.2.1] = null then null else if Text.StartsWith([Hyperlink.2.1], "/") then "aka.ms" & [Hyperlink.2.1] else if Text.StartsWith([Hyperlink.2.1], "ly/") then "bit." & [Hyperlink.2.1] else [Hyperlink.2.1] |
Удалите столбец Hyperlink.1. Переименуйте запрос в Internet. Загрузите его в таблицу на лист Excel. Изучите, какие еще проблемы не были решены. Попробуйте обновить код самостоятельно, чтобы сократить число проблем.
Рис. 10. Таблица на листе Excel