Como fazer paginação de API REST no Power Query

Estou escrevendo esse artigo pois um tempo atrás tive que dar muitas voltas para encontrar uma forma de fazer a paginação de API REST utilizando a linguagem M do power query e não encontrei nada muito claro.

Sempre quando precisamos criar relatórios e temos várias fontes de dados, uma das melhores ferramentas que conheço é o Power Query, disponível no Excel, Power BI e outras ferramentas da Microsoft.

Quero ser direto ao ponto neste conteúdo, mas não deixarei linha a linha dos códigos que veremos aqui.

Para entender o contexto completo farei um simples sumário dos passos que daremos.

1. Conhecendo uma API REST
   • Entendendo a API de Exemplo que vamos utilizar
   • Como montar o cálculo da paginação
2. Como integrar o Power Query do Excel com uma API REST
   • Criando uma Conexão vazia no Power Query
   • Criando código para buscar dados da API
   • Atualizar dados da consulta num tabela
3. Funções na Linguagem M para fazer paginação de API REST no Power Query para Power BI e Excel
   • Melhorias no código na linguagem M para processo de paginação da API REST
   • Como obter o total de registros do conteúdo da requisição
   • Como obter os registros página por página de API REST com o Power Query
4. Paginando e unindo registros da API REST com linguagem M no Power Query
5. Código completo e conclusões

1. Conhecendo uma API REST

Não pretendo abordar profundamente o que é uma API REST, mas quero explanar o fluxo de uma requisição REST para trabalharmos com o Power Query;

APIs REST são um padrão de arquitetura de software que tem um conjunto de regras e padrões para construção de web services. Basicamente um protocolo de comunicação.

Essa comunicação prevê que um “cliente” faça uma requisição para um “servidor” e receba uma resposta.

No nosso exemplo vamos trabalhar com um método HTTP GET, para obter dados de uma API.

Entendendo API de exemplo que vamos utilizar

Neste exemplo, vamos utilizar uma API do Sienge Plataforma, que é um software para construtoras e incorporadoras. Mas a regra se aplicará a qualquer API REST que tenha uma paginação.

O Link da documentação da API do Sienge Plataforma está aqui.

Vamos utilizar a API de Credores, onde a resposta da requisição tem seguinte definição:

{
  "resultSetMetadata": {
    "count": 0,
    "offset": 0,
    "limit": 0
  },
  "results": [
    {
      "id": 0,
      "name": "string",
      "tradeName": "string",
      "cpf": "string",
      "cnpj": "string",
      "supplier": "string",
      "broker": "string",
      "employee": "string"
    }
  ]
}

Veja que temos na resposta o resultSetMetadata que tem 3 informações importantes:

• count: Total geral de registros que utilizaremos para fazer a paginação
• offset: Que é o deslocamento da quantidade de registros que vamos ignorar pra buscar os próximos
• limit: Que é a quantidade de registros em cada página

Com essas informações será fácil sabermos quantas páginas teremos que percorrer para atingir toda a lista de registros.

Como montar o cálculo de paginação

"resultSetMetadata": {
    "count": 3965,
    "offset": 0,
    "limit": 200
},

Conforme código acima, podemos definir que o total de páginas é: resultSetMetadata.count / resultSetMetadata.limit ou (3.965 / 200 = 19,825). Para conseguirmos obter todos os registros arredondamos esse resultado para cima, ou seja 20 páginas.

Dentro de results temos a lista com os credores da página atual.

2. Como integrar o Power Query do Excel com uma API REST

Antes de qualquer coisa, vamos utilizar o Excel neste exemplo, porém no Power BI é exatamente igual.

A primeira coisa que vamos analisar é a versão do Excel e se ele tem suporte para o Power Query. Existe uma forma de instalar ele no seu computador neste link aqui;

Abaixo detalhes dos requisitos do sistema para instalar.

Depois de tudo pronto, vamos abrir um novo documento do Excel e criar uma Conexão com a API seguindo os passos abaixo:

Criando uma Conexão Vazia no Power Query

Com uma nova planilha aberta, clique no menu Dados > Obter Dados > Iniciar o Editor do Power Query conforme imagem abaixo:

Depois com a tela do Power Query aberta, vá no menu Página Inicial > Nova Fonte > Outros Fontes > Consulta Nula.

Então veremos a consulta criada e você poderá clicar com o botão direto e na opção Editor Avançado, conforme imagem abaixo.

Criando código para buscar dados da API

Agora vamos criar o código mais simples possível para colocar os dados da API na Planilha. Vamos usar a função da Linguagem M Web.Contents().

Web.Contents(url as text, optional options as nullable record) as binary

Eu preciso passar uma url completa do endpoint e opcionalmente o parâmetro options que tem uma série de configurações interessantes que serão úteis para atendermos as diversas particularidades das APIs. Veja a lista completa aqui.

Neste exemplo vamos usar a option Headers.

Desta forma vamos criar o seguinte código. O cabeçalho Authorization do Sienge Plataforma é Basic. E as credencias são obtidas dentro do software. Basicamente você junta nome de usuário e a senha com : (dois pontos) e criptografa em base64. Como exemplo: base64(usuario:senha) > dXN1YXJpbzpzZW5oYQ==; Vamos ver uma forma de resolver e dinamizar isto próximo tópico. Você também pode ver neste outro artigo sobre como dinamizar o token Basic utilizando o base64 na linguagem M.

let
    url = "https://api.sienge.com.br/subdomain-do-cliente-sienge/public/api/v1/creditors?",
    cabecalhos = [
          #"Authorization" = "Basic dXN1YXJpbzpzZW5oYQ==",
          #"Content-Type" = "application/json"
    ],
    resposta = Web.Contents(
        url,
        [
            Headers = cabecalhos
        ]
    ),
    respostaJson = Json.Document(resposta)
in
    respostaJson

Dentro do “let” nós criamos algumas variáveis pra facilitar a utilização e visualização do código. Inicialmente as duas informações que precisamos na Web.Contents().

Os cabeçalhos Authorization e Content-Type são os exigidos nesta API, mas poderão ter outros ou ser diferentes se requisitar outra API.

A Web.Contents() retorna um binário, como diz a documentação da Microsoft e para transformar isso em um JSON, como é o pretendido, precisamos usar a Json.Document().

Abaixo do “in” apresentamos o dado de saída para a consulta, ou seja, o JSON conforme modelo apresentado na documentação.

Atualizar dados da consulta numa tabela

Agora com esse código pronto e com o indicador de erro de sintaxe ok, vamos clicar em concluído para vermos como ficou a consulta.

Caso seja a primeira vez que se conecta neste arquivo no modo anônimo, precisará clicar em “Editar Credenciais”.

E mantendo o tipo de conexão em Anônimo, clique em conectar.

Você verá este resultado abaixo com as informações da API.

Note acima que temos o resultSetMetadata como um Record (Registro) e o results como um List (Lista). Se você quiser ver o conteúdo deles basta clicar nos respectivos links.

Se fosse uma API BULK DATA sem paginação o trabalho já estaria quase concluído, mas neste caso temos que percorrer algumas páginas até listar todos os dados e é isto que vamos ver nos próximos tópicos.

3. Funções na linguagem M para fazer paginação de API REST no Power Query para Power BI ou Excel

Agora vamos voltar para o código para adicionar algumas tratativas. Mas preciso explicar antes qual abordagem vamos utilizar.

Utilizaremos um mecanismo para fazer várias requisições sequenciais até obter todas as páginas, e vamos armazenar todas as páginas, depois vamos uni-las e retornar os dados.

Antes de iniciar precisamos fazer mapear as alterações que faremos neste código já montado.

Melhorias no código na linguagem M para o processo de paginação da API REST

Primeiramente vamos criar uma variável para informar a quantidade máximo de registros por página que nossa API aceita. Neste caso será 200.

RegistrosPorPagina = 200,

Como faremos várias requisições para obter as páginas, vamos criar uma função que faça isso. Se chamará obterResultadoJson, receberá um parâmetro chamado Url e fará a requisição e retornando o Json com todas as informações do resultSetMetadata e results.

obterResultadoJson = (Url) =>
  let opcoes = [Headers=[ 
                     #"Authorization" = "Basic dXN1YXJpbzpzZW5oYQ==",
                     #"Content-Type" = "application/json"
                ]],
      dadosConteudo = Web.Contents(Url, opcoes),
      dadosJson    = Json.Document(dadosConteudo)
  in  
      dadosJson,

Como obter o total de registros do conteúdo da requisição

Como já sabemos fazer este cálculo, vamos direto ao código explicado.

obterTotalRegistros = () =>
        let Url   = url & "&offset=0",
            Json  = obterResultadoJson(Url),
            dados = Json[#"resultSetMetadata"],
            quantidade = dados[#"count"] 
        in  
            quantidade,

Criamos um função que retorna para obterTotalRegistros o valor de resultSetMetadata.count, que é onde temos o valor da quantidade de registros. Essa função concatena a url básica com o parâmetro offset que indicará o deslocamento de registros.

Na sequência já invocamos a função obterResultadoJson passando esta nova Url;

Depois pegamos o objeto Json[#"resultSetMetadata"] e dentro dele pegamos o campo dados[#"count"] e retornamos o seu valor.

Como obter os registros página por página de uma API REST com Power Query

Agora a última função desta coleção será para utilizarmos quando estivermos chamando página por página.

obterRegistrosDaPagina = (pagina) =>
        let offset  = "&offset=" & Text.From(pagina * RegistrosPorPagina),
            limit   = "&limit=" & Text.From(RegistrosPorPagina),
            Url   = url & offset & "&" & limit,
            Json  = obterResultadoJson(Url),
            resultados = Json[#"results"]
        in  
            resultados,

A função obterRegistrosDaPagina recebe o parâmetro de qual página será requisitada, começando do 0 (zero) até N.

Calculamos o offset que é o número da pagina atual multiplicada pela quantidade de registros por página que definimos. Isso por que o parâmetro offset não espera o número da página e sem o deslocamento dos registros, ou seja, a segunda página do deslocamento de uma paginação de 200 registros por página seria 200, terceira pagina, seria 400 e assim por diante.

Depois definimos o limit para que tenhamos a mesma quantidade em todas as páginas, no caso desta API, se eu não definir este parâmetro o valor padrão é 100 registros por página.

Na sequencia montamos a Url final para invocar novamente a função obterResultadoJson e pegando o Json[#"results"] que é onde estão os registros dos credores.

4. Paginando e unindo registros da API REST com linguagem M no Power Query

Estamos chegando ao final deste tutorial e agora vamos mostrar como podemos utilizar essas função para fazer a paginação.

totalRegistros = List.Max({ RegistrosPorPagina, obterTotalRegistros() }),
numeroDePaginas   = Number.RoundUp(totalRegistros / RegistrosPorPagina),

Nestas duas primeiras linhas, nós definimos a variável que receberá o total de registros da API. Perceba que usamos a função List.Max() que recebe uma lista de valores e retorna o maior deles. Isso por que se tivermos menos de 200 registros iríamos ter menos de uma página.

Depois dividimos esse total pela quantidade de registros. E usamos a função Number.RoundUp( ) para arredondar para cima o número de páginas, pois se tivermos alguma página com menos de 200 registros a API não entenderá números fracionários.

indicesDasPaginas = { 0 .. numeroDePaginas - 1 },

Depois criamos um índice com todas as páginas que vamos percorrer. Usamos o -1 pois a API exige que comecemos pelo 0 (zero). Essa variável terá o valor de uma lista de números sequenciais.

paginas = List.Transform(indicesDasPaginas, each obterRegistrosDaPagina(_)),

Agora nós vamos de fato paginar. Tendo os índices, podemos percorrer essa lista com o each passando o próprio para a função obterRegistrosDaPagina(_) e adicionando à variável paginas.

paginasUnidas = List.Union(paginas),
tabela = Table.FromList(paginasUnidas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Depois disso, pegamos esse array de páginas e usamos a função List.Union( ) para unir as páginas que foram requisitadas.

Depois transformamos essa lista de paginas em uma tabela para retornarmos para o Power Query;

5. Código completo e conclusões

Com todas as explicações, vou apresentar o código completo e a exibição dos dados na planilha do Excel.

let
    url = "https://api.sienge.com.br/subdomain-do-cliente-sienge/public/api/v1/creditors?",
    RegistrosPorPagina = 200,
    cabecalhos = [
          #"Authorization" = "Basic dXN1YXJpbzpzZW5oYQ==",
          #"Content-Type" = "application/json"
    ],
    obterResultadoJson = (Url) =>
        let opcoes = [Headers=[ 
                           #"Authorization" = "Basic dXN1YXJpbzpzZW5oYQ==",
                           #"Content-Type" = "application/json"
                      ]],
            dadosConteudo = Web.Contents(Url, opcoes),
            dadosJson    = Json.Document(dadosConteudo)
        in  
            dadosJson,
     obterTotalRegistros = () =>
        let Url   = url & "&offset=0",
            Json  = obterResultadoJson(Url),
            dados = Json[#"resultSetMetadata"],
            quantidade = dados[#"count"] 
        in  
            quantidade,
     obterRegistrosDaPagina = (pagina) =>
        let offset  = "&offset=" & Text.From(pagina * RegistrosPorPagina),
            limit   = "&limit=" & Text.From(RegistrosPorPagina),
            Url   = url & offset & "&" & limit,
            Json  = obterResultadoJson(Url),
            resultados = Json[#"results"]
        in  
            resultados,
    totalRegistros = List.Max({ RegistrosPorPagina, obterTotalRegistros() }),
    numeroDePaginas   = Number.RoundUp(totalRegistros / RegistrosPorPagina),
    indicesDasPaginas = { 0 .. numeroDePaginas - 1 },
    paginas       = List.Transform(indicesDasPaginas, each obterRegistrosDaPagina(_)),
    paginasUnidas    = List.Union(paginas),
    tabela       = Table.FromList(paginasUnidas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 Expandido" = Table.ExpandRecordColumn(tabela, "Column1", {"id", "name", "tradeName", "cpf", "cnpj", "supplier", "broker", "employee", "links"}, {"Column1.id", "Column1.name", "Column1.tradeName", "Column1.cpf", "Column1.cnpj", "Column1.supplier", "Column1.broker", "Column1.employee", "Column1.links"}) 
in
    #"Column1 Expandido"

Ao adicionarmos este código teremos o exemplo de retorno, como abaixo:

Quando clicarmos em Fechar e carregar, já teremos os 3005 registros em apenas uma visualização como imagem abaixo:

Incrível, não é mesmo?

Existem algumas limitações deste cenário como por exemplo o rate limit que as APIs podem ter que podem controlar a quantidade de requisições que você pode fazer por minuto. Mas é uma abordagem que poderá facilitar a forma com que você faz relatórios.