Файлы к уроку:
Все уроки курса Веб-запросы в Power Query находятся на этой странице и в этом плейлисте.
Ссылки на страницы, к которым мы будем подключаться находятся по ссылке 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, чтобы извлечь список всех веб-страниц сайта. |
