Power Query. Получить данные из The Lord of the Rings API

• YouTube
• VK

Описание

Этот урок о том как в Power Query работать с API на примере The Lord of the Rings API. Сначала мы получим список фильмов и персонажей, а потом весь список реплик всех персонажей всех фильмов.

Решение

API описано по ссылке — https://the-one-api.dev/documentation

• url — https://the-one-api.dev/v2
• path
  • quote
  • movie
  • character

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

• Json.Document
• Web.Contents
• Value.Metadata
• Number.From
• Function.InvokeAfter
• List.Generate
• List.Combine
• Table.FromRecords
• Table.NestedJoin
• JoinKind.LeftOuter
• Table.ExpandTableColumn
• Table.SelectColumns

Код

Пример получения данных о фильмах

let
    data =
      Json.Document(
        Web.Contents("https://the-one-api.dev/v2",
                     [RelativePath="movie",
                      Headers= [Accept = "application/json",
                                Authorization = "Bearer " & "SlMNWO2lVjrP1aPAU8uz"]
                     ]
        )
      ),
    Пользовательский1 = data[docs],
    expanded = Table.FromRecords( Пользовательский1 )[[name],[_id]]
in
    expanded

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

let
    data =
      Json.Document(
        Web.Contents("https://the-one-api.dev/v2",
                     [RelativePath="character",
                      Headers= [Accept = "application/json",
                                Authorization = "Bearer " & "SlMNWO2lVjrP1aPAU8uz"]
                     ]
        )
      ),
    Пользовательский1 = data[docs],
    expanded = Table.FromRecords( Пользовательский1 )[[name],[_id]]
in
    expanded

Пример получения данных о цитатах

Функция получения текущей страницы:

(optional p_page)=>
let
    json =
      Web.Contents("https://the-one-api.dev/v2", [
        Headers =
          [Accept = "application/json",
           Authorization = "Bearer " & "SlMNWO2lVjrP1aPAU8uz"],
           RelativePath = "quote",
        Query = [page = Text.From(p_page ?? 1)]
      ]),
    read_json = Json.Document( json )[docs] meta [pg =Json.Document( json )[page] ]
in
    read_json

Функция получения следующей страницы:

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

Получение цитат:

let
  pages = 
    List.Generate(
      ()=> f_get_cur_quotes(1),
      each _ <> {},
      each f_get_next_quotes(_)
    ),
    combined = List.Combine(pages),
    expanded = Table.FromRecords(combined)[[dialog],[movie],[character]]
in
    expanded

Итоговый запрос:

let
    Источник = Table.NestedJoin(quotes, {"movie"}, movies, {"_id"}, "movies", JoinKind.LeftOuter),
    #"Объединенные запросы" = Table.NestedJoin(Источник, {"character"}, characters, {"_id"}, "characters", JoinKind.LeftOuter),
    #"Развернутый элемент movies" = Table.ExpandTableColumn(#"Объединенные запросы", "movies", {"name"}, {"movie_name"}),
    #"Развернутый элемент characters" = Table.ExpandTableColumn(#"Развернутый элемент movies", "characters", {"name"}, {"character_name"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент characters",{"movie_name", "character_name", "dialog"})
in
    #"Другие удаленные столбцы"