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

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

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

Ссылки:

Описание

Получим таблицу с вакансиями с сайта 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 строк.
2HTML, получение данных по облигациямМы хотим получить данные по всем облигациям со страницы bonds.finam.ru
3Многостраничное извлечениеНа разных страницах находятся сайта находятся котировки золота на каждый год. Нужно соединить все данные в одну таблицу.
4JSON, Котировки Yahoo Finance 2В этом уроке мы разберем еще один способ скачивания котировок с Yahoo Finance. Этот способ намного удобнее и быстрее. Здесь мы научимся обрабатывать формат JSON.
5Котировки Московской биржиВ этом уроке мы научимся получать прямо в Excel котировки разных ценных бумаг с сайта Московской биржи.
Московская биржа предоставляет возможность скачивать котировки и другую информацию по ценным бумагам при помощи специальных ссылок.
Если мы введем такую ссылку в Power Query, создав запрос из интернета, то получим интересующие нас котировки прямо в Excel.
6Неразмеченный текстВы делаете запрос к Web-странице и сталкиваетесь с неразмеченным текстом. Вы хотите при помощи Power Query этот неразмеченный текст преобразовать в нормальную красивую таблицу.
7Текстовый документ, веб-страница, List.ZipВ этом запросе мы научимся получать нужную информацию из текста веб-страницы.
8Youtube 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, чтобы извлечь список всех веб-страниц сайта.

Вас может заинтересовать

Power Query Веб-запросы №2. HTML, получение данных по облигациям

Power Query Ошибки №19. Неверная разбивка на столбцы

Power Query + VBA №1. Обновить запросы выборочно, обновить все запросы кроме одного

Power Query. Формулы М. Введение 1

Power Query Базовый №8. Объединить по горизонтали с агрегированием (Аналог JOIN + GROUP BY)

Power Query. Формулы М. Введение 3

Power Query. Обработка PDF выписки

Power Query. Более 2000 вакансий на лист Excel (API HH)

Power Query Базовый №14. Ссылка на другую строку 2

Power Query Веб-запросы №3. Многостраничное извлечение