Power Query Веб-запросы №15. Запрос к XML Sitemap (Карта сайта)

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

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

• YouTube
• VK

Ссылки:

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

Описание

Как получить список всех страниц веб-сайта? На некоторых веб-сайтах есть страница sitemap.xml. Перечень всех URL можно взять с этой страницы и потом сделать запрос к каждой из них.

Получим список всех статей Криса Веба из категории Power Query.

Решение

Сделаем запрос к странице https://blog.crossjoin.co.uk/sitemap-1.xml. Именно здесь находится карта с перечнем всех веб-страниц этого сайта.

Создадим функцию, которая с каждой страницы извлечет только заголовок статьи. Далее создадим функцию, которая с каждой страницы извлечет список категорий, к которым относится статья.

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

• Xml.Tables
• Web.Contents
• Table.SelectRows
• Table.AddColumn
• Text.Type
• List.Type
• Table.ExpandListColumn
• Table.TransformColumns
• Date.From
• Text.Start
• Text.Combine
• Lines.FromBinary
• Text.BetweenDelimiters
• Text.Split
• List.Transform

Код

Функция, которая извлекает заголовок статьи с веб-страницы.

(url)=>
let
   html = Text.Combine(
      {
         Lines.FromBinary(
            Web.Contents(url)
         )
      }{0}
   ), 
   text_substr = Text.BetweenDelimiters(html, "<h1 class=""entry-title"">", "</h1>")
in
   text_substr

Функция для извлечения категории статьи.

(url) =>
   let
      html = Text.Combine({Lines.FromBinary(Web.Contents(url))}{0}), 
      text_substr = Text.BetweenDelimiters(
         html, 
         "<span class=""entry-categories"">in", 
         "</span>"
      ), 
      list_categories = Text.Split(text_substr, ","), 
      list_transform = List.Transform(
         list_categories, 
         each Text.BetweenDelimiters(_, ">", "<")
      )
   in
      list_transform

Итоговый запрос.

let
   tabs = Xml.Tables(Web.Contents("https://blog.crossjoin.co.uk/sitemap-1.xml")), 
   get_urls = tabs{0}[Table], 
   rows_select_1 = Table.SelectRows(
      get_urls, 
      each (
         [loc]
            <> "https://blog.crossjoin.co.uk/" and [loc]
            <> "https://blog.crossjoin.co.uk/about/"
      )
   ), 
   fn_get_title = Table.AddColumn(
      rows_select_1, 
      "title", 
      each fn_get_title([loc]), 
      Text.Type
   ), 
   fn_get_category = Table.AddColumn(
      fn_get_title, 
      "category", 
      each fn_get_category([loc]), 
      List.Type
   ), 
   col_expand = Table.ExpandListColumn(fn_get_category, "category"), 
   rows_select_2 = Table.SelectRows(col_expand, each [category] = "Power Query"), 
   col_type = Table.TransformColumns(
      rows_select_2, 
      {{"lastmod", each Date.From(Text.Start(_, 10)), type date}}
   )
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, чтобы извлечь список всех веб-страниц сайта.