Power Query Веб-запросы №11. Получить таблицу станций метро на лист Excel

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

Разберем несколько способов получения списка станций метро прямо на лист Excel. Таких способов есть несколько.

Решение

Разберем как это сделать с помощью сервисов от HH, SuperJob, ЦИАН, Авито.

Примененные функции

• Int64.Type
• Json.Document
• Table.AddColumn
• Table.ExpandListColumn
• Table.ExpandRecordColumn
• Table.ExpandTableColumn
• Table.FromRecords
• Table.RemoveColumns
• Table.RenameColumns
• Table.SelectColumns
• Table.SelectRows
• Table.TransformColumnTypes
• Web.Contents
• Xml.Tables

Код

Получаем таблицу со станциями с помощью HeadHunter.

let
   get_data = Json.Document(Web.Contents("https://api.hh.ru/metro")), 
   get_table = Table.FromRecords(get_data), 
   col_expand_1 = Table.ExpandListColumn(get_table, "lines"), 
   tab_add_col_1 = Table.AddColumn(
      col_expand_1, 
      "line", 
      each [lines][name]
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "stations", 
      each [lines][stations]
   ), 
   col_expand_2 = Table.ExpandListColumn(tab_add_col_2, "stations"), 
   col_expand_3 = Table.ExpandRecordColumn(
      col_expand_2, 
      "stations", 
      {"id", "name", "lat", "lng", "order"}, 
      {
         "station_id", 
         "station_name", 
         "station_lat", 
         "station_lng", 
         "station_order"
      }
   ), 
   col_remove = Table.RemoveColumns(col_expand_3, {"lines"}), 
   cols_types = Table.TransformColumnTypes(
      col_remove, 
      {
         {"id", Int64.Type}, 
         {"station_id", type number}, 
         {"station_lat", type number}, 
         {"station_lng", type number}, 
         {"station_order", Int64.Type}
      }, 
      "en_US"
   )
in
   cols_types

Список станций с сайта SuperJob.

let
   get_data = Json.Document(
      Web.Contents("https://api.superjob.ru/2.0/suggest/town/4/metro/all/")
   ), 
   get_records = get_data[objects], 
   get_table = Table.FromRecords(get_records), 
   col_expand = Table.ExpandRecordColumn(
      get_table, 
      "line", 
      {"id", "title"}, 
      {"line_id", "line_title"}
   )
in
   col_expand

Список городов с сайта SuperJob.

let
   get_data = Json.Document(
      Web.Contents("https://api.superjob.ru/2.0/towns/?all=1&genitive=1")
   ), 
   get_records = get_data[objects], 
   get_table = Table.FromRecords(get_records)
in
   get_table

Список станций с сайта ЦИАН.

let
   get_data = Xml.Tables(
      Web.Contents("https://www.cian.ru/metros.php"), 
      null, 
      1251
   ), 
   get_table = get_data{0}[Table], 
   col_type = Table.TransformColumnTypes(
      get_table, 
      {{"Attribute:id", Int64.Type}}
   )
in
   col_type

Список станций метро с сайта Авито.

let
   get_data = Xml.Tables(
      Web.Contents("http://autoload.avito.ru/format/Locations.xml")
   ), 
   get_table = get_data{0}[Table], 
   cols_select = Table.SelectColumns(
      get_table, 
      {"Subway", "Attribute:Name"}
   ), 
   rows_select = Table.SelectRows(cols_select, each [Subway] <> null), 
   col_expand = Table.ExpandTableColumn(
      rows_select, 
      "Subway", 
      {"Attribute:Id", "Attribute:Name"}, 
      {"Attribute:Id", "Attribute:Name.1"}
   ), 
   col_rename = Table.RenameColumns(
      col_expand, 
      {
         {"Attribute:Id", "station_id"}, 
         {"Attribute:Name.1", "station_name"}, 
         {"Attribute:Name", "city"}
      }
   ), 
   col_type = Table.TransformColumnTypes(
      col_rename, 
      {{"station_id", Int64.Type}}
   )
in
   col_type

Этот урок входит в курс Веб-запросы в Power Query

Номер урока	Урок	Описание
1	Котировки Yahoo Finance	Вы хотите получить историю котировок акций MCD с 2000 года по текущий день с сайта Yahoo Finance. Вы копируете ссылку на раздел с историческими данными, вставляете ссылку в Power Query и получаете только 100 строк.
2	HTML, получение данных по облигациям	Мы хотим получить данные по всем облигациям со страницы bonds.finam.ru
3	Многостраничное извлечение	На разных страницах находятся сайта находятся котировки золота на каждый год. Нужно соединить все данные в одну таблицу.
4	JSON, Котировки Yahoo Finance 2	В этом уроке мы разберем еще один способ скачивания котировок с Yahoo Finance. Этот способ намного удобнее и быстрее. Здесь мы научимся обрабатывать формат JSON.
5	Котировки Московской биржи	В этом уроке мы научимся получать прямо в Excel котировки разных ценных бумаг с сайта Московской биржи. Московская биржа предоставляет возможность скачивать котировки и другую информацию по ценным бумагам при помощи специальных ссылок. Если мы введем такую ссылку в Power Query, создав запрос из интернета, то получим интересующие нас котировки прямо в Excel.
6	Неразмеченный текст	Вы делаете запрос к Web-странице и сталкиваетесь с неразмеченным текстом. Вы хотите при помощи Power Query этот неразмеченный текст преобразовать в нормальную красивую таблицу.
7	Текстовый документ, веб-страница, List.Zip	В этом запросе мы научимся получать нужную информацию из текста веб-страницы.
8	Youtube Data API	В этом уроке мы научимся получать данные из Youtube Data API. Узнаем как получить данные о Youtube канале: список видео и плейлистов, названия, описания, длительность, тэги, количество лайков, дизлайков, просмотров и комментариев.
9	Личный OneDrive — подключаемся напрямую к файлам и папкам	В этом уроке мы разберем как напрямую подключиться к файлам и папкам на личном OneDrive.
10	Подключение Google Spreadsheets, Google Drive (Excel.Workbook, Web.Contents)	В этом уроке мы научимся подключаться к xlsx файлам, которые находятся в вашем Google Drive.
11	Получить все станции метро	Получим таблицу с перечнем станций метрополитена даже с координатами широты и долготы.
12	Получить ссылки с веб-страницы (Html.Table)	Получить URL с веб-страницы. Сделать запрос к каждому URL, получить таблицы и объединить их всех по вертикали.
13	Получить все URL с веб-страницы	Получим нужные URL с веб-страницы в Excel. Сделать запрос к каждому URL, получить таблицы и объединить их по вертикали.
14	Ищем работу через API Head Hunter (hh.ru)	Получим таблицу с перечнем вакансий с сайте HH. Получим вакансии, в названии или описании которых встречаются слова SQL, Pandas, Power Query, Power Pivot, Power BI.
15	Запрос к XML Sitemap	Сделаем запрос к sitemap.xml, чтобы извлечь список всех веб-страниц сайта.