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

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

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

• YouTube
• VK

Ссылки:

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

Описание

На разных страницах сайта находятся котировки золота на каждый год. Нужно соединить все данные в одну таблицу.

При помощи Power Query мы получим табличные данные с каждой веб-страницы, а потом объединим их по вертикали в одну большую таблицу.

Решение

Для решения мы воспользуемся следующим:

• Web.Page
• Web.Contents
• Список из чисел
• Table.FromList
• Ссылки

Формула для получения данных для одного года будет следующей:

= Web.Page(Web.Contents("http://www.calc.ru/kotirovka-dollar-ssha.html?date=2000")){[ClassName="result_table"]}[Data]

Нам нужно эту формулу применить к каждому году с 2000 по 2018. Для этого вместо вместо подстроки «2000» мы будем передавать параметр из отдельного столбца. К каждой строке этого столбца мы применить эту формулу.

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

• Table.FromList
• Splitter.SplitByNothing
• Table.AddColumn
• Web.Page
• Web.Contents
• Text.From
• Table.ExpandTableColumn
• Table.TransformColumnTypes
• Table.UnpivotOtherColumns
• Table.CombineColumns
• Combiner.CombineTextByDelimiter
• QuoteStyle.None
• Table.SelectRows
• Table.NestedJoin
• JoinKind.LeftOuter
• Table.RemoveColumns
• Table.Sort
• Table.ReplaceValue
• Replacer.ReplaceValue

Код

let
   list_years = Table.FromList(
      {2000 .. 2018}, 
      Splitter.SplitByNothing(), 
      {"Год"}
   ), 
   tab_add_col = Table.AddColumn(
      list_years, 
      "Пользовательская", 
      each Web.Page(
         Web.Contents(
            "http://www.calc.ru/kotirovka-dollar-ssha.html?date="
               & Text.From([Год])
               & ""
         )
      ){[ClassName = "result_table"]}[Data]
   ), 
   col_expand = Table.ExpandTableColumn(
      tab_add_col, 
      "Пользовательская", 
      {"Дата", "Курс"}, 
      {"Дата", "Курс"}
   ), 
   col_type = Table.TransformColumnTypes(col_expand, {{"Дата", 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, чтобы извлечь список всех веб-страниц сайта.