Power Query Веб-запросы №7. Текстовый документ, веб-страница, List.Zip

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

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

• YouTube
• VK

Ссылки:

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

Ссылки на страницы, к которым мы будем подключаться находятся по ссылке https://www.cftc.gov/MarketReports/CommitmentsofTraders/index.htm

Описание

Данные находятся по ссылке https://www.cftc.gov/dea/newcot/deafut.txt

В данных нет заголовков.

Заголовки находятся по ссылке https://www.cftc.gov/MarketReports/CommitmentsofTraders/HistoricalViewable/cotvariableslegacy.html

Нужно соединить эти данные, чтобы получилась одна таблица.

Решение

Чтобы решить задачу мы изучим/повторим:

• Подключение к web-странице
• List.Zip
• Table.RenameColumns
• Table.ColumnNames
• Ссылки

Функция Table.RenameColumns в качестве второго параметра принимает список списков. Каждый список — это пара значений: старое название — новое название.

Для создания списков пар значений и понадобится функция List.Zip.

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

• Web.Contents
• Csv.Document
• QuoteStyle.None
• Table.RenameColumns
• List.Zip
• Web.Page
• Table.ExpandTableColumn
• Table.TransformColumns
• Text.AfterDelimiter
• Table.SelectRows

Код

Итоговая таблица:

let
   source = Csv.Document(
      Web.Contents("http://www.cftc.gov/dea/newcot/deafut.txt"), 
      [
         Delimiter  = ",", 
         Columns    = 129, 
         Encoding   = 1251, 
         QuoteStyle = QuoteStyle.None
      ]
   ), 
   cols_rename = Table.RenameColumns(
      source, 
      List.Zip({Table.ColumnNames(source), headers[Text]})
   )
in
   cols_rename

Получение таблицы с заголовками:

let
   source = Web.Page(
      Web.Contents(
         "http://www.cftc.gov/MarketReports/CommitmentsofTraders/HistoricalViewable/cotvariableslegacy.html"
      )
   ), 
   Data = source{0}[Data], 
   Children = Data{0}[Children], 
   rows_select_1 = Table.SelectRows(Children, each ([Name] = "BODY")), 
   get_table_1 = rows_select_1{0}[Children], 
   rows_select_2 = Table.SelectRows(get_table_1, each ([Name] = "DIV")), 
   get_table_2 = rows_select_2{0}[Children], 
   rows_select_3 = Table.SelectRows(get_table_2, each ([Name] = "DIV")), 
   get_table_3 = rows_select_3{0}[Children], 
   rows_select_4 = Table.SelectRows(get_table_3, each ([Name] = "DIV")), 
   get_table_4 = rows_select_4{0}[Children], 
   rows_select_5 = Table.SelectRows(get_table_4, each ([Name] = "SECTION")), 
   get_table_5 = rows_select_5{0}[Children], 
   rows_select_6 = Table.SelectRows(get_table_5, each ([Name] = "DIV")), 
   get_table_6 = rows_select_6{0}[Children], 
   rows_select_7 = Table.SelectRows(get_table_6, each ([Name] = "ARTICLE")), 
   get_table_7 = rows_select_7{0}[Children], 
   rows_select_8 = Table.SelectRows(get_table_7, each ([Name] = "DIV")), 
   get_table_8 = rows_select_8{0}[Children], 
   rows_select_9 = Table.SelectRows(get_table_8, each ([Name] = "DIV")), 
   get_table_9 = rows_select_9{0}[Children], 
   rows_select_10 = Table.SelectRows(get_table_9, each ([Name] = "CENTER")), 
   get_table_10 = rows_select_10{0}[Children], 
   rows_select_11 = Table.SelectRows(get_table_10, each ([Name] = "TABLE")), 
   get_table_11 = rows_select_11{0}[Children], 
   get_table_12 = get_table_11{0}[Children], 
   get_table_13 = get_table_12{1}[Children], 
   rows_select_12 = Table.SelectRows(get_table_13, each ([Name] = "TD")), 
   get_table_14 = rows_select_12{0}[Children], 
   rows_select_13 = Table.SelectRows(get_table_14, each ([Name] = "P")), 
   cols_select = Table.SelectColumns(rows_select_13, {"Children"}), 
   col_expand = Table.ExpandTableColumn(
      cols_select, 
      "Children", 
      {"Text"}, 
      {"Text"}
   ), 
   col_transform = Table.TransformColumns(
      col_expand, 
      {{"Text", each Text.AfterDelimiter(_, " "), type text}}
   ), 
   rows_select_14 = Table.SelectRows(
      col_transform, 
      each [Text] <> null and [Text] <> ""
   )
in
   rows_select_14

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