let
    // aqui é informado a primeira parte da url do link
    http = "https://transwar.lannister.emitir.me/",
    
    // a segunda parte da url, é o "endpoint" da consulta que se quer fazer
    endpoint = "collections/search?",        // Coletas

    // e aqui se monta a url (o '&' concatena as partes)
    url = http & endpoint,

    //aqui o nº de registros por página
    RegistrosPorPagina = 40,

    // começo da função que acessa o link
    obterResultadoJson = (Url) =>
        let opcoes = [Headers=[ 
                        #"x-api-key" = "75594207-b5d7-43bf-a7c2-571b3030308d",
                        #"Content-Type" = "application/json"
                    ]],
            dadosConteudo = Web.Contents(Url, opcoes),
            dadosJson    = Json.Document(dadosConteudo)
        in  
            dadosJson,
    // fim da função

    // começo da função que pega o total de registros
    obterTotalRegistros = () =>
        let Url   = url,
            Json  = obterResultadoJson(Url),
            quantidade = Json[#"total"] 
        in  
            quantidade,
    // fim da função

    // começo da função que pega os registros de cada página
    obterRegistrosDaPagina = (pagina) =>
        let page  = "&page=" & Text.From(pagina),
            Url   = url & page,
            Json  = obterResultadoJson(Url),
            resultados = Json[#"data"]
        in  
            resultados,
    // fim da função

    // a função List.Max pega o maior valor entre, o "total de registros" e o "nº de registros por página"
    totalRegistros = List.Max({ RegistrosPorPagina, obterTotalRegistros() }),

    // gera o nº de páginas arendondado pra mais
    numeroDePaginas   = Number.RoundUp(totalRegistros / RegistrosPorPagina),

    // cria uma lista com os números das páginas que vão ser lidas
    indicesDasPaginas = { 1 .. numeroDePaginas },

    // faz um looping lendo cada página da lista
    paginas = List.Transform(indicesDasPaginas, each obterRegistrosDaPagina(_)),

    // junta todas as páginas lidas
    paginasUnidas = List.Union(paginas),

    // transforma a lista em tabela
    tabela = Table.FromList(paginasUnidas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // começo da "Power Query" para definir e expandir as colunas da tabela 
    // essa é a parte que deve ser mudada para cada tipo de consulta...(CTe, notas, romaneios, etc.) 

    #"Column1 Expandido1" = Table.ExpandRecordColumn(tabela, "Column1", {"id", "owner_id", "serie", "number", "status", "obs", "requestor", "retired", "scheduling", "addressee", "last", "goods", "category1", "category2"}, {"Column1.id", "Column1.owner_id", "Column1.serie", "Column1.number", "Column1.status", "Column1.obs", "Column1.requestor", "Column1.retired", "Column1.scheduling", "Column1.addressee", "Column1.last", "Column1.goods", "Column1.category1", "Column1.category2"}),
    #"Column1.requestor Expandido" = Table.ExpandRecordColumn(#"Column1 Expandido1", "Column1.requestor", {"phone", "name", "email"}, {"Column1.requestor.phone", "Column1.requestor.name", "Column1.requestor.email"}),
    #"Column1.retired Expandido" = Table.ExpandRecordColumn(#"Column1.requestor Expandido", "Column1.retired", {"address"}, {"Column1.retired.address"}),
    #"Column1.retired.address Expandido" = Table.ExpandRecordColumn(#"Column1.retired Expandido", "Column1.retired.address", {"street", "district", "number", "complement"}, {"Column1.retired.address.street", "Column1.retired.address.district", "Column1.retired.address.number", "Column1.retired.address.complement"}),
    #"Column1.scheduling Expandido" = Table.ExpandRecordColumn(#"Column1.retired.address Expandido", "Column1.scheduling", {"retired"}, {"Column1.scheduling.retired"}),
    #"Column1.scheduling.retired Expandido" = Table.ExpandRecordColumn(#"Column1.scheduling Expandido", "Column1.scheduling.retired", {"limit_date"}, {"Column1.scheduling.retired.limit_date"}),
    #"Column1.addressee Expandido" = Table.ExpandRecordColumn(#"Column1.scheduling.retired Expandido", "Column1.addressee", {"phone", "name"}, {"Column1.addressee.phone", "Column1.addressee.name"}),
    #"Column1.last Expandido" = Table.ExpandRecordColumn(#"Column1.addressee Expandido", "Column1.last", {"print", "user"}, {"Column1.last.print", "Column1.last.user"}),
    #"Column1.goods Expandido" = Table.ExpandRecordColumn(#"Column1.last Expandido", "Column1.goods", {"quantity", "cubage", "weight", "esp", "net_weight", "length", "nf", "order_number", "value", "width", "height"}, {"Column1.goods.quantity", "Column1.goods.cubage", "Column1.goods.weight", "Column1.goods.esp", "Column1.goods.net_weight", "Column1.goods.length", "Column1.goods.nf", "Column1.goods.order_number", "Column1.goods.value", "Column1.goods.width", "Column1.goods.height"}),
    #"Column1.category1 Expandido" = Table.ExpandRecordColumn(#"Column1.goods Expandido", "Column1.category1", {"id"}, {"Column1.category1.id"}),
    #"Column1.category2 Expandido" = Table.ExpandRecordColumn(#"Column1.category1 Expandido", "Column1.category2", {"id"}, {"Column1.category2.id"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Column1.category2 Expandido",{{"Column1.id", Int64.Type}, {"Column1.owner_id", Int64.Type}, {"Column1.serie", Int64.Type}, {"Column1.number", Int64.Type}, {"Column1.status", Int64.Type}, {"Column1.obs", type text}, {"Column1.requestor.phone", type text}, {"Column1.requestor.name", type text}, {"Column1.requestor.email", type text}, {"Column1.retired.address.street", type text}, {"Column1.retired.address.district", type text}, {"Column1.retired.address.number", type text}, {"Column1.retired.address.complement", type text}, {"Column1.scheduling.retired.limit_date", type date}, {"Column1.addressee.phone", type text}, {"Column1.addressee.name", type text}, {"Column1.last.print", type datetime}, {"Column1.last.user", type text}, {"Column1.goods.quantity", Int64.Type}, {"Column1.goods.cubage", Int64.Type}, {"Column1.goods.weight", Int64.Type}, {"Column1.goods.esp", type text}, {"Column1.goods.net_weight", Int64.Type}, {"Column1.goods.length", Int64.Type}, {"Column1.goods.nf", Int64.Type}, {"Column1.goods.order_number", type text}, {"Column1.goods.value", Int64.Type}, {"Column1.goods.width", Int64.Type}, {"Column1.goods.height", Int64.Type}, {"Column1.category1.id", Int64.Type}, {"Column1.category2.id", type any}})
    
    // final da "Power Query"  

in
    #"Tipo Alterado"