Мультистраничный веб-запрос в Power Query
Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Предположим, например, что мы хотим загрузить в Excel результаты чемпионатов по гольфу с 2010 по 2021 годы из википедии со страницы https://en.wikipedia.org/wiki/Category:Golf_by_year
Каждая из выделенных жёлтым ссылок ведёт на страницу с результатами чемпионатов за соответствующий год, представленными в виде таблицы Table of results: При этом выбранный год участвует в адресе страницы и входит туда как фрагмент:
Чтобы собрать данные сразу за несколько лет, т.е. реализовать мультистраничный веб-запрос, нам придётся пройти следующие этапы:
- Создать сначала одиночный веб-запрос к любой странице из интересующего списка
- Создать параметр для года и внедрить его в запрос
- Преобразовать запрос в функцию (параметр станет её аргументом)
- Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
- Объединить полученные результаты в единую таблицу за все годы
Шаг 1. Одиночный веб-запрос
Сначала выполняем обычный классический веб-запрос к любой странице из нашего списка. Выбираем на вкладке Данные команду Получить данные - Из других источников - Из интернета (Get Data - From Other Sources - From Internet) и вставляем путь к исходной странице - например, к результатам за 2020 год со страницы https://en.wikipedia.org/wiki/2020_in_golf
В открывшемся затем окне Навигатора выбираем нужную нам таблицу и жмём кнопку Преобразовать или Изменить (Transform), чтобы передать её в редактор Power Query для очистки и дополнительных корректировок. Например, уберём отмененные из-за ковида чемпионаты (в столбце Winner у них стоит Canceled) и почистим лишние пробелы, невидимые символы и т.д. - всё, что вам захочется сделать потом со всеми остальными таблицами за другие годы.
Получившийся запрос назовём, например, Results of year:
Шаг 2. Создаем и внедряем параметр для года
Теперь создадим для года параметр - изменяемую переменную, которую затем внедрим в наш запрос. Для этого в окне редактора Power Query выбираем на вкладке Главная команду Управление параметрами - Создать параметр (Home - Manage Parameters - Create parameter) и задаём его характеристики:
Обратите внимание, что тип данных ставим текстовый (а не числовой), т.к. потом этот параметр (номер года) придётся подклеивать в адрес вебстраницы.
После создания параметра возвращаемся в наш первый запрос и жмём на символ шестерёнки справа от шага Источник, чтобы открыть окно с подробностями. В нём переключаемся в режим Подробнее (Advanced) и разделяем путь к исходной веб-странице на три фрагмента кнопкой Добавить часть (Add part). И второй фрагмент (номер года) - заменяем на созданный параметр Год:
Шаг 3. Преобразуем запрос в функцию
Теперь можно легко преобразовать наш запрос с внедрённым в него параметром в функцию, где параметр станет аргументом. Для этого щёлкаем по запросу Results of year правой кнопкой мыши и выбираем команду Создать функцию (Create function), а затем задаём ей имя - например, getGolfResults:
Шаг 4. Создаем список аргументов и вызываем функцию
Осталось создать список значений аргументов (т.е. лет) для которых мы хотим вызвать нашу функцию. Для этого можно сделать новый пустой запрос командой Получить данные - Из других источников - Пустой запрос (Get Data - From Other Sources - Blank Query) и ввести в строку формул выражение для создания списка числовых значений:
Если нужно будет сделать арифметическую прогрессию не с шагом 1, а хитрее, то можно использовать М-функцию List.Numbers ( https://learn.microsoft.com/en-us/powerquery-m/list-numbers ), а для создания последовательности дат функцию List.Dates ( https://learn.microsoft.com/en-us/powerquery-m/list-dates )
Работать со списком не очень удобно, так что лучше будет преобразовать его в таблицу одноименной кнопкой в левом верхнем углу. После этого осталось лишь вызвать созданную функцию на вкладке Добавление столбца - Вызвать настраиваемую функцию (Add Column - Invoke Custom Function), подставив ей номер года как аргумент (т.е. значение параметра):
Шаг 5. Объединяем всё в единую таблицу
После предыдущего шага мы получим столбец, в каждой ячейке которого будет таблица с результатами работы функции для соответствующего года. Чтобы объединить их всё в единый отчёт, используем кнопку с двойными стрелками в правом верхнем углу столбца:
Выбираем интересующие нас столбцы, жмём на ОК - и наша задача решена - результаты всех чемпионатов за 12 лет с 12 вебстраниц в одной таблице:
Что особенно приятно, если в будущем дизайн веб-страниц, откуда мы брали данные, изменится, то нам придётся подправить лишь наш первый запрос Results of year. Созданная на его основе с параметром функция getGolfResults изменится уже автоматически. Так что поддерживать всю эту систему сбора данных в будущем можно очень легко.
Ссылки по теме
- Обновляемый курс валют в Excel с функциями ВЕБСЛУЖБА и ФИЛЬТР.XML
- Система учёта заказов на Google Calendar и Excel с Power Query
- Импорт курса биткойна в Excel через Power Query
А можно как-то список лет брать с листа XL из умной таблицы?