Power Query умеет подключаться не только к web страничкам, но и к API, не требующим авторизации. Если API позволяет анонимным пользователям забирать данные, Power Query подойдёт идеально.
API компании ПИК - данные по квартирам
Подключимся к API застройщика, чтобы отслеживать актуальную стоимость квартиры. Находим нужную квартиру на сайте застройщика: https://beta.pik.ru/flat/801472.
"https://api.pik.ru/v1/flat?id=" - URL адрес, по которому застройщик предоставляет информацию, а "801472" – идентификатор квартиры.
Информация на сайте показана красиво, но разбросана по разным вкладкам.
Мы же хотим получить лаконичную табличку в Excel:
| Адрес |
| Стоимость |
| Скидка |
| Заселение до |
| Площадь |
| Комнаты |
| Этаж |
| Ссылка |
GET-запрос к API ПИК на Power Query
Для разнообразия воспользуюсь Power Query в Excel. Создаем пустой запрос:
В строке формул пишем:
Json.Document(Web.Contents("https://api.pik.ru/v1/flat?id=801472"))В результате получаем строку с вложенными составными элементами, которые также могут содержать вложенные элементы:
Называем запрос «getData_oneFlat» и вставляем пустой шаг после шага Источник (чтобы открыть меню - щелкаем правой кнопкой мышки на шаг Источник):
С помощью функции Record.FromList формируем строку с нужными данными, используя шаг Источник. В строке формул пишем:
Record.FromList(
{Источник[block][address], Источник[price], Источник[discount], Источник[bulk][date_till], Источник[area], Источник[rooms], Источник[floor], Источник[url], Источник[pdf]},
type [ Адрес = text, Стоимость = number, Скидка = number, Заселение до = text, Площадь = number, Комнаты = number, Этаж = number, Ссылка = text, PDF = text])Давайте разбираться как работает функция Record.FromList:
- Первый аргумент функции отвечает за содержимое полей – это список:
{Источник[block][address], Источник[price]} - Источник[price] – возвращает содержимое поля price
- Источник[block][address] – возвращает содержимое поля address из Record вложенной в поле block
- Второй аргумент отвечает за названия и тип полей – это строка:
type [Адрес = text, Стоимость = number]
Результат - строка нужных нам значений:
Преобразуем строку в таблицу:
Выгрузим результат на лист Excel и посмотрим что получилось:
Такой формат нас устроит, теперь мы видим всю информацию по квартире в удобной таблице.
Несколько GET-запросов с помощью функции
Теперь попробуем забрать сразу несколько квартир и разместить их в одной табличке.
Анонсы всех видео, статей и полезностей - в нашем Telegram-канале🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Для этого убираем последний шаг и создаём функцию getData от параметра flat (номер квартиры), заменяя сам номер на "flat":
(flat as text)=>
let
Источник = Json.Document(Web.Contents("https://api.pik.ru/v1/flat?id="& flat )),
create_table = Record.FromList(
{Источник[block][address],
Источник[price],
Источник[discount],
Источник[bulk][date_till],
Источник[area],
Источник[rooms],
Источник[floor],
Источник[url],
Источник[pdf]
},
type [
Адрес = text,
Стоимость = number,
Скидка = number,
Заселение до = text,
Площадь = number,
Комнаты = number,
Этаж = number,
Ссылка = text,
PDF = text
])
in
create_tableТеперь наша функция будет получать данные по той квартире, которую мы укажем в параметре.
Составляем на листе Excel список интересных нам квартир:
Загружаем список квартир в Power Query, выставляем столбцу текстовый тип. Затем добавляем новый столбец с вызовом нашей функции от каждой квартиры в списке, получается такой код:
let
Источник = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Получаемые квартиры", type text}}),
#"Вызвана настраиваемая функция" = Table.AddColumn(#"Измененный тип", "getData", each getData([Получаемые квартиры]))
in
#"Вызвана настраиваемая функция"Таблица в Power Query выглядит так:
Нажимаем на стрелочки справа от "getData" и разворачиваем нужные столбцы, а затем выгружаем всё на лист Excel. Теперь у нас есть табличка со стоимостью, скидками, площадью и датами заселения для всех интересных нам объектов:
Заключение
Пример вызова API сайта компании ПИК показывает как Power Query может быть полезен в бытовых задачах. Используйте PQ если вам нужно быть в курсе изменений данных, будь то стоимость квартиры или цена товара в интернет-магазине.
Если компания предоставляет данные по открытому API, вы всегда можете использовать магию Power Query и вывести эти данные себе в Excel-файл. Можно сравнить несколько товаров/категорий или вообще написать алгоритм, который будет отображать только самые интересные товары.
Комментарии