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 = "ctes/search?created_from=2023-09-01",            // CTes

    // 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", "serie", "number", "owner_id", "key", "nf_total_weight_calculated", "issued_at", "status", "delivered_at", "taker", "cashreport_id", "lat_long_trust_average", "last_print", "registration", "expiration", "total", "ciot", "issurance_secondary_registration", "nf_total_amount", "nf_total_weight", "freight_weight", "freight_value", "authorized_at", "icms", "collect_rate", "delivery_rate", "quotation_id", "invoice_id", "delivery_at", "delivery2_at", "delivery3_at", "driver_id", "vehicle_traction_license_plate", "vehicle_trailer1_license_plate", "vehicle_trailer2_license_plate", "vehicle_trailer3_license_plate", "created_at", "process_id", "updated_at", "customer_id", "insurance", "service_type", "operation_id", "costcenter_id", "input_origin", "gnre", "owner_name", "owner_abbreviation", "cfop", "sender_name", "addressee_name", "dispatcher_name", "receiver_name", "customer_name", "invoice_number", "quotation_number", "process_number", "cashreport_number", "driver_name", "billing_profile_name", "create_user", "update_user", "send_user", "cancel_user", "initial_city_id", "final_city_id", "initial_uf_id", "final_uf_id", "origin_mdfe", "destination_mdfe", "addressee_cpf_or_cnpj", "sender_cpf_or_cnpj", "outcome_id", "outcome_number", "outcome_type"}, {"Column1.id", "Column1.serie", "Column1.number", "Column1.owner_id", "Column1.key", "Column1.nf_total_weight_calculated", "Column1.issued_at", "Column1.status", "Column1.delivered_at", "Column1.taker", "Column1.cashreport_id", "Column1.lat_long_trust_average", "Column1.last_print", "Column1.registration", "Column1.expiration", "Column1.total", "Column1.ciot", "Column1.issurance_secondary_registration", "Column1.nf_total_amount", "Column1.nf_total_weight", "Column1.freight_weight", "Column1.freight_value", "Column1.authorized_at", "Column1.icms", "Column1.collect_rate", "Column1.delivery_rate", "Column1.quotation_id", "Column1.invoice_id", "Column1.delivery_at", "Column1.delivery2_at", "Column1.delivery3_at", "Column1.driver_id", "Column1.vehicle_traction_license_plate", "Column1.vehicle_trailer1_license_plate", "Column1.vehicle_trailer2_license_plate", "Column1.vehicle_trailer3_license_plate", "Column1.created_at", "Column1.process_id", "Column1.updated_at", "Column1.customer_id", "Column1.insurance", "Column1.service_type", "Column1.operation_id", "Column1.costcenter_id", "Column1.input_origin", "Column1.gnre", "Column1.owner_name", "Column1.owner_abbreviation", "Column1.cfop", "Column1.sender_name", "Column1.addressee_name", "Column1.dispatcher_name", "Column1.receiver_name", "Column1.customer_name", "Column1.invoice_number", "Column1.quotation_number", "Column1.process_number", "Column1.cashreport_number", "Column1.driver_name", "Column1.billing_profile_name", "Column1.create_user", "Column1.update_user", "Column1.send_user", "Column1.cancel_user", "Column1.initial_city_id", "Column1.final_city_id", "Column1.initial_uf_id", "Column1.final_uf_id", "Column1.origin_mdfe", "Column1.destination_mdfe", "Column1.addressee_cpf_or_cnpj", "Column1.sender_cpf_or_cnpj", "Column1.outcome_id", "Column1.outcome_number", "Column1.outcome_type"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Column1 Expandido1",{{"Column1.id", Int64.Type}, {"Column1.serie", Int64.Type}, {"Column1.number", Int64.Type}, {"Column1.owner_id", Int64.Type}, {"Column1.key", type number}, {"Column1.nf_total_weight_calculated", Int64.Type}, {"Column1.issued_at", type datetime}, {"Column1.status", Int64.Type}, {"Column1.delivered_at", type any}, {"Column1.taker", Int64.Type}, {"Column1.cashreport_id", type any}, {"Column1.lat_long_trust_average", Int64.Type}, {"Column1.last_print", type datetime}, {"Column1.registration", type number}, {"Column1.expiration", type date}, {"Column1.total", Int64.Type}, {"Column1.ciot", type any}, {"Column1.issurance_secondary_registration", type any}, {"Column1.nf_total_amount", Int64.Type}, {"Column1.nf_total_weight", Int64.Type}, {"Column1.freight_weight", Int64.Type}, {"Column1.freight_value", Int64.Type}, {"Column1.authorized_at", type datetime}, {"Column1.icms", Int64.Type}, {"Column1.collect_rate", Int64.Type}, {"Column1.delivery_rate", Int64.Type}, {"Column1.quotation_id", type any}, {"Column1.invoice_id", type any}, {"Column1.delivery_at", type date}, {"Column1.delivery2_at", type any}, {"Column1.delivery3_at", type any}, {"Column1.driver_id", Int64.Type}, {"Column1.vehicle_traction_license_plate", type text}, {"Column1.vehicle_trailer1_license_plate", type any}, {"Column1.vehicle_trailer2_license_plate", type any}, {"Column1.vehicle_trailer3_license_plate", type any}, {"Column1.created_at", type datetime}, {"Column1.process_id", type any}, {"Column1.updated_at", type datetime}, {"Column1.customer_id", Int64.Type}, {"Column1.insurance", Int64.Type}, {"Column1.service_type", Int64.Type}, {"Column1.operation_id", Int64.Type}, {"Column1.costcenter_id", Int64.Type}, {"Column1.input_origin", type any}, {"Column1.gnre", type any}, {"Column1.owner_name", type text}, {"Column1.owner_abbreviation", type text}, {"Column1.cfop", Int64.Type}, {"Column1.sender_name", type text}, {"Column1.addressee_name", type text}, {"Column1.dispatcher_name", type text}, {"Column1.receiver_name", type text}, {"Column1.customer_name", type text}, {"Column1.invoice_number", type any}, {"Column1.quotation_number", type any}, {"Column1.process_number", type any}, {"Column1.cashreport_number", type any}, {"Column1.driver_name", type text}, {"Column1.billing_profile_name", type text}, {"Column1.create_user", type text}, {"Column1.update_user", type text}, {"Column1.send_user", type text}, {"Column1.cancel_user", type text}, {"Column1.initial_city_id", Int64.Type}, {"Column1.final_city_id", Int64.Type}, {"Column1.initial_uf_id", Int64.Type}, {"Column1.final_uf_id", Int64.Type}, {"Column1.origin_mdfe", type text}, {"Column1.destination_mdfe", type text}, {"Column1.addressee_cpf_or_cnpj", Int64.Type}, {"Column1.sender_cpf_or_cnpj", Int64.Type}, {"Column1.outcome_id", Int64.Type}, {"Column1.outcome_number", Int64.Type}, {"Column1.outcome_type", Int64.Type}, {"message", type any}})
    
    // final da "Power Query"  

in
    #"Tipo Alterado"