Power Query. Работа с Fire and Ice API

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

Описание

Этот урок о том как в Power Query работать с API на примере Fire and Ice API. Сначала мы получим список книг серии, а потом получим информацию обо всех персонажах серии.

Решение

API описано по ссылке — https://github.com/joakimskoog/AnApiOfIceAndFire/wiki.

url —https://www.anapioficeandfire.com/api
path
- Books — получить информацию о книгах
- Characters — получить информацию о персонажах
- Houses — получить информацию о домах

Авторизация не требуется. Получение информации обо всех книгах и домах не составит труда. Чтобы получить информацию обо всех персонажах нужно воспользоваться функцией List.Generate.

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

Web.Contents
Text.From
Json.Document
List.Transform
Value.Metadata
Request.Options
Number.From
Function.InvokeAfter
List.Combine
Table.FromList
Table.ExpandRecordColumn

Код получения данных о книгах

Параметры, которые созданы как отдельные запросы.

Запрос url

"https://www.anapioficeandfire.com/api"

Запрос path

"books"

Запрос parameters

[
    pageSize = "50"
]

Итоговый запрос Книги

let
    json =
      Web.Contents(url, [
        RelativePath = path,
        Query = parameters
      ]),
    read_json = Json.Document( json ),
    to_table = Table.FromList(read_json, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    extract_table = Table.ExpandRecordColumn(to_table, "Column1", {"url", "name", "isbn", "authors", "numberOfPages", "publisher", "country", "mediaType", "released", "characters", "povCharacters"}, {"url", "name", "isbn", "authors", "numberOfPages", "publisher", "country", "mediaType", "released", "characters", "povCharacters"})
in
    extract_table

Код получения данных о персонажах

Параметры также созданы как отдельные запросы.

Запрос url.

"https://www.anapioficeandfire.com/api"

Запрос path.

Для примера получим мертвых персонажей мужского пола.

[
    pageSize = "50",
    page = "1",
    gender = "Male",
    isAlive = "false"
]

Запрос Персонажи.

let
    json =
      Web.Contents(url, [
        Headers = headers,
        RelativePath = path,
        Query = parameters
      ]),
    read_json = Json.Document( json ),
    to_table = Table.FromList(read_json, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    col_expand = Table.ExpandRecordColumn(to_table, "Column1", {"url", "name", "gender", "culture", "born", "died", "titles", "aliases", "father", "mother", "spouse", "allegiances", "books", "povBooks", "tvSeries", "playedBy"})
in
    col_expand

Код получения данных обо всех персонажах

В дополнение к предыдущему запросу создадим 2 функции: одна для получения данных для текущей страницы, а вторая для получения данных для следующей страницы.

Функция f_get_characters_cur.

(optional page)=>
let
    json =
      Web.Contents(url, [
        Headers = headers,
        RelativePath = path,
        Query = parameters & [page = Text.From(page ?? 1)]
      ]),
    read_json = Json.Document( json ),
    add_page = List.Transform(read_json, each _ & [page = Value.Metadata( json )[Request.Options][Query][page]])
              meta [pg = Value.Metadata( json )[Request.Options][Query][page]]
in
    add_page

Функция f_get_characters_next.

(prev) =>
let
  metadata = Value.Metadata( prev ),
  page = Number.From( metadata[pg] ),
  next = Function.InvokeAfter( ()=> f_get_characters_cur( page + 1 ), #duration(0, 0, 0, 0.05) )
in
  next

Запрос Персонажи.

let
  pages = 
    List.Generate(
    ()=> f_get_characters_cur(1),
    each _ <> {},
    each f_get_characters_next(_)
  ),
  
  combined = List.Combine(pages),
  
  to_table =
    Table.FromList(combined,
                   Splitter.SplitByNothing(),
                   null,
                   null,
                   ExtraValues.Error),
  
  col_expand =
    Table.ExpandRecordColumn(to_table,
                             "Column1",
                             {"url",
                              "name",
                              "gender",
                              "culture",
                              "born",
                              "died",
                              "titles",
                              "aliases",
                              "father",
                              "mother",
                              "spouse",
                              "allegiances",
                              "books",
                              "povBooks",
                              "tvSeries",
                              "playedBy",
                              "page"})
in
    col_expand

Другие уроки из курса Разное

Номер урока	Урок	Описание
1	Power Query. Знакомство с Power Query	В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос
2	Power Query. Подключение XML	В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel.
3	Power Query. Уникальные значения двух столбцов	В этом уроке мы получим уникальные значения из двух столбцов таблицы.
4	Power Query. Импорт таблиц PDF	Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет.
5	Power Query. Собрать разбитую строку	В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала.
6	Power Query. Пивот со счетом	В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы.
7	Power Query. Минимальное значение в диапазоне	В этом уроке мы найдем минимальное значение в диапазоне строк.
8	Power Query. Нарастающий итог 2	В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query.
9	Power Query. Нарастающий итог 3	В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query.
10	Power Query. Прирост населения Китая	В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет.
11	Power Query. Повторяющиеся значения в строке	В этом уроке разберем как определить есть ли в строке повторения.
12	Power Query. Таблица навигации по функциям М	В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query.
13	Power Query. Удалить запросы и модель данных из книги	Разберем как быстро удалить все запросы и модель данных из текущей книги.
14	Power Query. Открыть еще 1 Excel и еще 3 трюка	В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query.
15	Power Query. Подключиться к ZIP архиву	Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве.
16	Power Query. Импорт Word	Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками.
17	Power Query. Фильтрация списком	В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров.
18	Power Query. Пользовательская функция Switch	В этом уроке мы создадим пользовательскую функцию Switch.
19	Power Query. Информация о формате, Чтение zip	В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
20	Power Query. Импорт данных из gz	В этом уроке мы разберем как импортировать файл в формате gz.
21	Power Query. Удалить лишние пробелы, Text.Split	В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы.
22	Power Query. Параметры в SQL-запросе	Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel.
23	Power Query. Параметры в SQL-запросе 2	Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать.
24	Power Query. Добавить столбец в каждую таблицу табличного столбца	В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца.
25	Power Query. Интервальный просмотр 1 (ВПР 1)	Объединить 2 таблицы с интервальным просмотром = 1.
26	Power Query. Относительный путь к файлу и папке	Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере.
27	Power Query. Нарастающий итог в каждой категории	Применим функцию нарастающего итога не ко всей таблице, а к определенному окну.
28	Power Query. ВПР без Merge или Join	Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.

Номер урока	Урок	Описание
29	Power Query. Создать N копий выбранного столбца	Создадим функцию, чтобы пользователь мог указать нужное количество копий.
30	Power Query. Текст содержит подстроку из списка (Text.ContainsAny)	Нам нужно, чтобы отфильтровались строки, в которых содержится любая подстрока из списка.
31	Power Query. Генерация таблицы для веб-запросов	С помощью List.Generate сгенерируем таблицу для запросов к API
32	Power Query. Фильтрация табличного столбца с помощью столбца таблицы	Нужно отфильтровать табличный столбец с помощью другого столбца.
33	Power Query. Количество вхождений подстроки	Нужно посчитать сколько раз подстрока входит в строку.
34	Power Query. Скученные данные 4	Еще один интересный пример работы со скученными данными.
35	Power Query. Антизаполнение / Unfill	То же самое, что Fill Down, но наоборот: вместо значений вернуть null.
36	Power Query. Получить код из текста	Есть текстовый файл с М-кодом. Как подключиться к файлу и выполнить этот код.
37	Power Query. Повторить строку N раз	Если строку нужно повторить определенное количество раз.
38	Power Query. Обработка PDF выписки	PDF выписку с результатами медицинских анализов преобразуем в таблицу.
39	Power Query. Объединить по вертикали файлы из разных папок	Папки с нужными файлами находятся в разных места.
40	Power Query. Объединение со знаками сравнения	Объединить таблицы по горизонтали со знаками сравнения.
41	Power Query. ABC анализ по категориям	ABC анализ отдельно для каждой категории.
42	Power Query. Более 2000 вакансий на лист Excel (API HH)	Дополним урок о получении вакансий HH. Разберем как получить более 2000 вакансий.
43	Power Query. Получить данные из Ice and Fire API	Учимся работать с API на примере Ice and Fire API.

предыдущая запись

Excel тренинг. Сводные таблицы

следующая запись