Power Query Веб-запросы №6. Неразмеченный текст

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

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

• YouTube
• VK

Ссылки:

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

Описание

Вы делаете запрос к Web-странице и сталкиваетесь с неразмеченным текстом. Вы хотите при помощи Power Query этот неразмеченный текст преобразовать в нормальную красивую таблицу.

Решение

Для решения нам понадобится:

• Text.Split
• Table.FromRows
• Table.ExpandListColumn
• Text.Contains
• Table.Transpose
• List.Select
• Text.BetweenDelimiters

Подключиться к этому html документу нужно как к тексту. Мы получим код html, который сначала нужно будет очистить от всего лишнего. После удаления всех html тэгов из текста мы приступим к поиску закономерностей в структуре таблицы.

Если в строке есть подстрока «Code», то в таком случае на 3 строки ниже мы можем найти заголовки первого уровня таблицы, на 5 строк ниже заголовки второго уровня таблицы, а на 9 строк ниже мы найдем значения.

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

• Table.FromColumns
• Lines.FromBinary
• Web.Contents
• Table.TransformColumns
• Text.Trim
• Table.SelectRows
• Text.StartsWith
• Table.AddIndexColumn
• Table.AddColumn
• Text.Contains
• Text.Split
• Table.ExpandListColumn
• Table.Transpose
• Table.FromRows
• List.Select
• Text.BetweenDelimiters
• Table.TransformColumnTypes
• Table.SelectColumns
• Text.BeforeDelimiter
• Table.ExpandTableColumn
• Table.CombineColumns
• Combiner.CombineTextByDelimiter
• QuoteStyle.None
• Table.RenameColumns
• Table.Pivot
• List.Distinct
• List.Sum

Код

let
   source = Table.FromColumns(
      {
         Lines.FromBinary(
            Web.Contents("https://www.cftc.gov/dea/futures/deacmesf.htm")
         )
      }
   ), 
   col_transform = Table.TransformColumns(
      source, 
      {{"Column1", Text.Trim, type text}}
   ), 
   rows_select_1 = Table.SelectRows(
      col_transform, 
      each [Column1] <> null and [Column1] <> ""
   ), 
   rows_select_2 = Table.SelectRows(
      rows_select_1, 
      each not Text.StartsWith([Column1], "<")
   ), 
   tab_add_index = Table.AddIndexColumn(rows_select_2, "Индекс", 0, 1), 
   tab_add_col_1 = Table.AddColumn(
      tab_add_index, 
      "Заголовки0", 
      each 
         if Text.Contains([Column1], "Code") then
            Text.Split(tab_add_index[Column1]{[Индекс] + 3}, "|")
         else
            null
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "Повторы", 
      each 
         if Text.Contains([Column1], "Code") then
            {{1 .. 3}, {1, 2}, {1, 2}, {1, 2}}
         else
            null
   ), 
   tab_add_col_3 = Table.AddColumn(
      tab_add_col_2, 
      "Заголовки1", 
      each 
         if Text.Contains([Column1], "Code") then
            Table.ExpandListColumn(
               Table.Transpose(Table.FromRows({[Заголовки0], [Повторы]})), 
               "Column2"
            )[Column1]
         else
            null
   ), 
   tab_add_col_4 = Table.AddColumn(
      tab_add_col_3, 
      "Заголовки2", 
      each 
         if Text.Contains([Column1], "Code") then
            Text.Split(tab_add_col_3[Column1]{[Индекс] + 5}, "|")
         else
            null
   ), 
   tab_add_col_5 = Table.AddColumn(
      tab_add_col_4, 
      "Данные", 
      each 
         if Text.Contains([Column1], "Code") then
            List.Select(
               Text.Split(tab_add_col_4[Column1]{[Индекс] + 9}, " "), 
               each _ <> ""
            )
         else
            null
   ), 
   tab_add_col_6 = Table.AddColumn(
      tab_add_col_5, 
      "Дата", 
      each 
         if Text.Contains([Column1], "Code") then
            Text.BetweenDelimiters(
               tab_add_col_5[Column1]{[Индекс] + 1}, 
               "OF", 
               "|"
            )
         else
            null
   ), 
   col_type = Table.TransformColumnTypes(
      tab_add_col_6, 
      {{"Дата", type date}}, 
      "en-US"
   ), 
   tab_add_col_7 = Table.AddColumn(
      col_type, 
      "Пользовательская", 
      each 
         if Text.Contains([Column1], "Code") then
            Table.Transpose(
               Table.FromRows({[Заголовки1], [Заголовки2], [Данные]})
            )
         else
            null
   ), 
   col_select = Table.SelectColumns(
      tab_add_col_7, 
      {"Дата", "Пользовательская", "Column1"}
   ), 
   rows_select = Table.SelectRows(col_select, each ([Дата] <> null)), 
   col_transform_2 = Table.TransformColumns(
      rows_select, 
      {{"Column1", each Text.BeforeDelimiter(_, " -"), type text}}
   ), 
   col_expand = Table.ExpandTableColumn(
      col_transform_2, 
      "Пользовательская", 
      {"Column1", "Column2", "Column3"}, 
      {"Column1.1", "Column2", "Column3"}
   ), 
   cols_transform = Table.TransformColumns(
      col_expand, 
      {
         {"Column1.1", Text.Trim, type text}, 
         {"Column2", Text.Trim, type text}
      }
   ), 
   cols_combine = Table.CombineColumns(
      cols_transform, 
      {"Column1.1", "Column2"}, 
      Combiner.CombineTextByDelimiter("; ", QuoteStyle.None), 
      "Позиция"
   ), 
   cols_rename = Table.RenameColumns(
      cols_combine, 
      {{"Column3", "Сумма"}, {"Column1", "Продукт"}}
   ), 
   cols_type = Table.TransformColumnTypes(
      cols_rename, 
      {{"Сумма", type number}}
   ), 
   tab_pivot = Table.Pivot(
      cols_type, 
      List.Distinct(cols_type[Позиция]), 
      "Позиция", 
      "Сумма", 
      List.Sum
   )
in
   tab_pivot

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