Power Query Веб-запросы №14. Получить вакансии на лист Excel с hh.ru (API HH)

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

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

• YouTube
• VK

Ссылки:

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

Описание

Получим таблицу с вакансиями с сайта hh.ru. Получим название вакансии, название компании, ближайшую станцию метро, описание, обязанности, зарплату и другую информацию.

В нашем примере нас интересуют вакансии в тексте которых встречаются ключевые слова:

• Power Query
• Power Pivot
• Pandas
• SQL
• Tableau
• Power BI

Решение

Сначала заготовим таблицу следующего вида.

Создадим запрос к url https://api.hh.ru/vacancies. В качества параметров номера страницы и номера вакансии сошлемся на столбцы заготовленной таблицы.

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

• Table.FromRows
• List.Transform
• Table.ExpandListColumn
• Table.AddColumn
• Json.Document
• Web.Contents
• Text.From
• Table.TransformColumns
• Table.ExpandTableColumn
• Table.ExpandRecordColumn
• Table.TransformColumnTypes
• DateTimeZone.Type
• Table.SelectRows
• Table.RemoveColumns
• Table.FromRecords
• Table.SelectColumns

Код

Параметр hh_query.

" ""power+query""+OR+""power+pivot""+OR+""power+bi""+OR+""vba""+OR+""pandas""+OR+""tableau""+""sql"" "

Параметр hh_area.

"1"

Функция для получения данных для одной вакансии.

(rec)=>
let
   rec_to_table = Table.FromRecords({rec}), 
   cols_select = Table.SelectColumns(
      rec_to_table, 
      {
         "address", 
         "alternate_url", 
         "employer", 
         "id", 
         "name", 
         "published_at", 
         "salary", 
         "schedule", 
         "snippet"
      }
   ), 
   col_expand_1 = Table.ExpandRecordColumn(cols_select, "address", {"metro"}), 
   col_expand_2 = Table.ExpandRecordColumn(
      col_expand_1, 
      "metro", 
      {"station_name", "line_name", "station_id", "line_id", "lat", "lng"}, 
      {
         "metro_station_name", 
         "metro_line_name", 
         "metro_station_id", 
         "metro_line_id", 
         "metro_lat", 
         "metro_lng"
      }
   ), 
   col_expand_3 = Table.ExpandRecordColumn(
      col_expand_2, 
      "employer", 
      {"alternate_url", "name"}, 
      {"emp_url", "emp_name"}
   ), 
   col_expand_4 = Table.ExpandRecordColumn(
      col_expand_3, 
      "schedule", 
      {"name"}, 
      {"schedule"}
   ), 
   col_expand_5 = Table.ExpandRecordColumn(
      col_expand_4, 
      "snippet", 
      {"requirement", "responsibility"}
   )
in
   col_expand_5

Запрос итоговой таблицы.

let
   create_table = Table.FromRows(
      List.Transform({0 .. 19}, each {_, {0 .. 99}}), 
      {"page_num", "vac_num"}
   ), 
   col_expand = Table.ExpandListColumn(create_table, "vac_num"), 
   tab_add_col = Table.AddColumn(
      col_expand, 
      "get_data", 
      each Json.Document(
         Web.Contents(
            "http://api.hh.ru/vacancies?text="
               & hh_query
               & "&area="
               & hh_area
               & "&per_page=100&page="
               & Text.From([page_num])
         )
      )[items]{[vac_num]}
   ), 
   col_transform = Table.TransformColumns(
      tab_add_col, 
      {{"get_data", each fn_get_vacancy(_)}}
   ), 
   col_expand_1 = Table.ExpandTableColumn(
      col_transform, 
      "get_data", 
      {
         "metro_station_name", 
         "metro_line_name", 
         "metro_station_id", 
         "metro_line_id", 
         "metro_lat", 
         "metro_lng", 
         "alternate_url", 
         "emp_url", 
         "emp_name", 
         "id", 
         "name", 
         "published_at", 
         "salary", 
         "schedule", 
         "requirement", 
         "responsibility"
      }, 
      {
         "metro_station_name", 
         "metro_line_name", 
         "metro_station_id", 
         "metro_line_id", 
         "metro_lat", 
         "metro_lng", 
         "alternate_url", 
         "emp_url", 
         "emp_name", 
         "id", 
         "name", 
         "published_at", 
         "salary", 
         "schedule", 
         "requirement", 
         "responsibility"
      }
   ), 
   col_expand_2 = Table.ExpandRecordColumn(
      col_expand_1, 
      "salary", 
      {"from", "to"}, 
      {"salary_from", "salary_to"}
   ), 
   cols_types = Table.TransformColumnTypes(
      col_expand_2, 
      {{"metro_station_id", type number}, {"metro_line_id", type number}, {"published_at", DateTimeZone.Type}}, 
      "en_US"
   ),
    rows_select = Table.SelectRows(cols_types, each [metro_station_name] <> null and [metro_station_name] <> ""),
    cols_select = Table.RemoveColumns(rows_select,{"page_num", "vac_num"})
in
   cols_select

Этот урок входит в курс Веб-запросы в 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, чтобы извлечь список всех веб-страниц сайта.