erro no carregamento de dados do power query para o power bi.

[Eduardo_Braga]

Olá a todos, estou tendo utilizando uma integração via API para retornar os dados do sistema SIIMP,  no power query consegui acessar todas as informações de forma correta, mas quando vou enviar esses dadso para o Power Bi os dados vem fragimentados, por exemplo, tenho uma tabela que no power query me apresenta 15000 linhas, mas quando aplico no BI ela só me retorna 800 ou 600 ou até menos.
Além disso existem tabelas que me retornam como se a tabela estivesse null, mesmo tendo sido atualizada a alguns minutos.
As vezes carrega 100%, as vezes não.

Vou expor um modelo para que possamo analisar melhor:

 

abaixo deixei o arquivo PBIXSiimp principal.pbix

 

[Bruno Abdalla de Souza]

Pesquise sobre a API.e veja se.ela não tem restrição de volume de dados.

Não é problema com o ETL.

[Alex Alvares]

Olá, não consegui fazer nada pelo teu PBIX, mas vou deixar um teste para você fazer.
Tomara que de certo.
Abraço.

[Bruno Abdalla de Souza]

Para acessar o PowerQuery precisaremos da sua base de dados tb.

[Eduardo_Braga]

3 horas atrás, Bruno Abdalla de Souza disse:

Para acessar o PowerQuery precisaremos da sua base de dados tb.

Podem acessar o PBIX e no Query ajuste a configuração para anônimo 

[Eduardo_Braga]

Os dados

12 horas atrás, Bruno Abdalla de Souza disse:

Para acessar o PowerQuery precisaremos da sua base de dados tb.

Os dados estão vindo via API, para acessar pode configurar o acesso como anônimo.

[Bruno Abdalla de Souza]

Tente usar este código.

let
    Fonte = fNfCTe(null, null),
    #"cte Expandido" = Table.ExpandRecordColumn(Fonte, "cte", {"id", "status", "number"}, {"cte.id", "cte.status", "cte.number"}),
    #"issuer Expandido" = Table.ExpandRecordColumn(#"cte Expandido", "issuer", {"id", "name", "cnpj", "address"}, {"issuer.id", "issuer.name", "issuer.cnpj", "issuer.address"}),
    #"issuer.address Expandido" = Table.ExpandRecordColumn(#"issuer Expandido", "issuer.address", {"street", "number", "complement", "district", "zipcode", "state", "city", "country"}, {"issuer.address.street", "issuer.address.number", "issuer.address.complement", "issuer.address.district", "issuer.address.zipcode", "issuer.address.state", "issuer.address.city", "issuer.address.country"}),
    #"issuer.address.state Expandido" = Table.ExpandRecordColumn(#"issuer.address Expandido", "issuer.address.state", {"ibge", "uf"}, {"issuer.address.state.ibge", "issuer.address.state.uf"}),
    #"issuer.address.city Expandido" = Table.ExpandRecordColumn(#"issuer.address.state Expandido", "issuer.address.city", {"ibge", "name"}, {"issuer.address.city.ibge", "issuer.address.city.name"}),
    #"issuer.address.country Expandido" = Table.ExpandRecordColumn(#"issuer.address.city Expandido", "issuer.address.country", {"code", "name"}, {"issuer.address.country.code", "issuer.address.country.name"}),
    #"addressee Expandido" = Table.ExpandRecordColumn(#"issuer.address.country Expandido", "addressee", {"id", "name", "cnpj", "address"}, {"addressee.id", "addressee.name", "addressee.cnpj", "addressee.address"}),
    #"addressee.address Expandido" = Table.ExpandRecordColumn(#"addressee Expandido", "addressee.address", {"street", "number", "district", "zipcode", "state", "city", "country", "complement"}, {"addressee.address.street", "addressee.address.number", "addressee.address.district", "addressee.address.zipcode", "addressee.address.state", "addressee.address.city", "addressee.address.country", "addressee.address.complement"}),
    #"addressee.address.state Expandido" = Table.ExpandRecordColumn(#"addressee.address Expandido", "addressee.address.state", {"ibge", "uf"}, {"addressee.address.state.ibge", "addressee.address.state.uf"}),
    #"addressee.address.city Expandido" = Table.ExpandRecordColumn(#"addressee.address.state Expandido", "addressee.address.city", {"ibge", "name"}, {"addressee.address.city.ibge", "addressee.address.city.name"}),
    #"addressee.address.country Expandido" = Table.ExpandRecordColumn(#"addressee.address.city Expandido", "addressee.address.country", {"code", "name"}, {"addressee.address.country.code", "addressee.address.country.name"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"addressee.address.country Expandido",{{"id", Int64.Type}, {"owner_id", Int64.Type}, {"serie", Int64.Type}, {"number", Int64.Type}, {"type", Int64.Type}, {"ncm", Int64.Type}, {"taker", Int64.Type}, {"cte.id", Int64.Type}, {"cte.status", Int64.Type}, {"cte.number", Int64.Type}, {"issuer.id", Int64.Type}, {"issuer.address.state.ibge", Int64.Type}, {"issuer.address.country.code", Int64.Type}, {"addressee.id", Int64.Type}, {"addressee.address.number", Int64.Type}, {"addressee.address.state.ibge", Int64.Type}, {"weight", type number}, {"qtd", type number}, {"total_net_value", type number}, {"suspended_tax", type number}, {"qtd_products", type number}, {"created_at", type datetime}, {"obs", type text}, {"esp", type text}, {"input_origin", type text}, {"issuer.name", type text}, {"issuer.cnpj", type text}, {"issuer.address.street", type text}, {"issuer.address.number", Int64.Type}, {"issuer.address.complement", type text}, {"issuer.address.district", type text}, {"issuer.address.state.uf", type text}, {"issuer.address.city.name", type text}, {"issuer.address.country.name", type text}, {"addressee.name", type text}, {"addressee.cnpj", type text}, {"addressee.address.street", type text}, {"addressee.address.district", type text}, {"addressee.address.zipcode", type text}, {"addressee.address.state.uf", type text}, {"addressee.address.city.ibge", type text}, {"addressee.address.city.name", type text}, {"issued_at", type date}, {"updated_at", type datetime}}),
    #"Linhas Classificadas1" = Table.Sort(#"Tipo Alterado",{{"created_at", Order.Ascending}}),
    #"Erros Substituídos" = Table.ReplaceErrorValues(#"Linhas Classificadas1", {{"issuer.address.number", null}}),
    #"Erros Substituídos1" = Table.ReplaceErrorValues(#"Erros Substituídos", {{"addressee.address.number", null}}),
    #"Erros Removidos" = Table.RemoveRowsWithErrors(#"Erros Substituídos1"),
    #"Colocar Cada Palavra Em Maiúscula" = Table.TransformColumns(#"Erros Removidos",{{"obs", Text.Proper, type text}, {"issuer.name", Text.Proper, type text}, {"issuer.address.street", Text.Proper, type text}, {"issuer.address.complement", Text.Proper, type text}, {"issuer.address.district", Text.Proper, type text}, {"issuer.address.state.uf", Text.Proper, type text}, {"issuer.address.city.name", Text.Proper, type text}, {"issuer.address.country.name", Text.Proper, type text}, {"addressee.name", Text.Proper, type text}, {"addressee.address.street", Text.Proper, type text}, {"addressee.address.district", Text.Proper, type text}, {"addressee.address.state.uf", Text.Proper, type text}, {"addressee.address.city.name", Text.Proper, type text}, {"addressee.address.country.name", Text.Proper, type text}}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Colocar Cada Palavra Em Maiúscula",{{"created_at", type date}, {"updated_at", type date}}),
    #"Duplicatas Removidas" = Table.Distinct(#"Tipo Alterado1"),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Duplicatas Removidas",{{"created_at", "Data Criação"}, {"owner_id", "Id Proprietário"}, {"total", "Total"}, {"issued_at", "Data Publicação"}, {"updated_at", "Data Atualização"}, {"net_weight", "Peso"}, {"type", "Tipo"}, {"cte.id", "Id CTE"}, {"cte.status", "Status CTE"}, {"cte.number", "Numero CTE"}, {"issuer.id", "Id Emissão"}, {"issuer.name", "Nome Emissor"}, {"issuer.cnpj", "CNPJ Emissor"}}),
    #"Colunas Mescladas" = Table.CombineColumns(Table.TransformColumnTypes(#"Colunas Renomeadas", {{"issuer.address.number", type text}}, "pt-BR"),{"issuer.address.street", "issuer.address.number"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Endereço Emissor"),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Colunas Mescladas",{{"issuer.address.complement", "Compl. Endereço emissor"}, {"issuer.address.district", "Bairro Emissor"}, {"issuer.address.zipcode", "CEP Emissor"}, {"issuer.address.state.uf", "Estado Emissor"}, {"issuer.address.city.name", "Cidade Emissor"}, {"issuer.address.country.name", "País Emissor"}, {"addressee.id", "Id Destinatário"}, {"addressee.name", "Destinatário"}, {"addressee.cnpj", "Cnpj Destinatário"}, {"addressee.address.district", "Bairro Destinatário"}, {"addressee.address.zipcode", "CEP Destinatário"}, {"addressee.address.state.uf", "Estado Destinatário"}, {"addressee.address.city.name", "Cidade Destinatário"}, {"addressee.address.country.name", "País Destinatário"}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Colunas Renomeadas1",{"addressee.address.complement", "total_net_value", "suspended_tax", "addressee.address.country.code", "addressee.address.city.ibge", "addressee.address.state.ibge"}),
    #"Colunas Mescladas1" = Table.CombineColumns(Table.TransformColumnTypes(#"Colunas Removidas", {{"addressee.address.number", type text}}, "pt-BR"),{"addressee.address.street", "addressee.address.number"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Endereço Destinatário"),
    #"Colunas Removidas1" = Table.RemoveColumns(#"Colunas Mescladas1",{"Id Destinatário", "issuer.address.country.code", "issuer.address.city.ibge", "issuer.address.state.ibge", "Compl. Endereço emissor", "taker", "qtd_products", "input_origin", "Tipo", "ncm", "esp", "key", "weight", "Bairro Emissor", "Bairro Destinatário"}),
    #"Colunas Renomeadas2" = Table.RenameColumns(#"Colunas Removidas1",{{"Numero CTE", "ID Viagem"}, {"Nome Emissor", "Remetente"}, {"CNPJ Emissor", "CNPJ Remetente"}, {"Endereço Emissor", "Endereço Remetente"}, {"Estado Emissor", "Estado Remetente"}, {"Cidade Emissor", "Cidade Remetente"}, {"País Emissor", "País Remetente"}, {"Total", "Valor Frete"}}),
    #"Valor Substituído" = Table.ReplaceValue(#"Colunas Renomeadas2",".","",Replacer.ReplaceText,{"Peso"}),
    #"Primeiros caracteres extraídos" = Table.TransformColumns(#"Valor Substituído", {{"Peso", each Number.From(Text.Start(_, 5)), type number}}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Primeiros caracteres extraídos",{{"Valor Frete", Currency.Type}})
in
    #"Tipo Alterado2"

 

20240716_Siimp principal.pbix

[Eduardo_Braga]

2 horas atrás, Bruno Abdalla de Souza disse:

Tente usar este código.

let
    Fonte = fNfCTe(null, null),
    #"cte Expandido" = Table.ExpandRecordColumn(Fonte, "cte", {"id", "status", "number"}, {"cte.id", "cte.status", "cte.number"}),
    #"issuer Expandido" = Table.ExpandRecordColumn(#"cte Expandido", "issuer", {"id", "name", "cnpj", "address"}, {"issuer.id", "issuer.name", "issuer.cnpj", "issuer.address"}),
    #"issuer.address Expandido" = Table.ExpandRecordColumn(#"issuer Expandido", "issuer.address", {"street", "number", "complement", "district", "zipcode", "state", "city", "country"}, {"issuer.address.street", "issuer.address.number", "issuer.address.complement", "issuer.address.district", "issuer.address.zipcode", "issuer.address.state", "issuer.address.city", "issuer.address.country"}),
    #"issuer.address.state Expandido" = Table.ExpandRecordColumn(#"issuer.address Expandido", "issuer.address.state", {"ibge", "uf"}, {"issuer.address.state.ibge", "issuer.address.state.uf"}),
    #"issuer.address.city Expandido" = Table.ExpandRecordColumn(#"issuer.address.state Expandido", "issuer.address.city", {"ibge", "name"}, {"issuer.address.city.ibge", "issuer.address.city.name"}),
    #"issuer.address.country Expandido" = Table.ExpandRecordColumn(#"issuer.address.city Expandido", "issuer.address.country", {"code", "name"}, {"issuer.address.country.code", "issuer.address.country.name"}),
    #"addressee Expandido" = Table.ExpandRecordColumn(#"issuer.address.country Expandido", "addressee", {"id", "name", "cnpj", "address"}, {"addressee.id", "addressee.name", "addressee.cnpj", "addressee.address"}),
    #"addressee.address Expandido" = Table.ExpandRecordColumn(#"addressee Expandido", "addressee.address", {"street", "number", "district", "zipcode", "state", "city", "country", "complement"}, {"addressee.address.street", "addressee.address.number", "addressee.address.district", "addressee.address.zipcode", "addressee.address.state", "addressee.address.city", "addressee.address.country", "addressee.address.complement"}),
    #"addressee.address.state Expandido" = Table.ExpandRecordColumn(#"addressee.address Expandido", "addressee.address.state", {"ibge", "uf"}, {"addressee.address.state.ibge", "addressee.address.state.uf"}),
    #"addressee.address.city Expandido" = Table.ExpandRecordColumn(#"addressee.address.state Expandido", "addressee.address.city", {"ibge", "name"}, {"addressee.address.city.ibge", "addressee.address.city.name"}),
    #"addressee.address.country Expandido" = Table.ExpandRecordColumn(#"addressee.address.city Expandido", "addressee.address.country", {"code", "name"}, {"addressee.address.country.code", "addressee.address.country.name"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"addressee.address.country Expandido",{{"id", Int64.Type}, {"owner_id", Int64.Type}, {"serie", Int64.Type}, {"number", Int64.Type}, {"type", Int64.Type}, {"ncm", Int64.Type}, {"taker", Int64.Type}, {"cte.id", Int64.Type}, {"cte.status", Int64.Type}, {"cte.number", Int64.Type}, {"issuer.id", Int64.Type}, {"issuer.address.state.ibge", Int64.Type}, {"issuer.address.country.code", Int64.Type}, {"addressee.id", Int64.Type}, {"addressee.address.number", Int64.Type}, {"addressee.address.state.ibge", Int64.Type}, {"weight", type number}, {"qtd", type number}, {"total_net_value", type number}, {"suspended_tax", type number}, {"qtd_products", type number}, {"created_at", type datetime}, {"obs", type text}, {"esp", type text}, {"input_origin", type text}, {"issuer.name", type text}, {"issuer.cnpj", type text}, {"issuer.address.street", type text}, {"issuer.address.number", Int64.Type}, {"issuer.address.complement", type text}, {"issuer.address.district", type text}, {"issuer.address.state.uf", type text}, {"issuer.address.city.name", type text}, {"issuer.address.country.name", type text}, {"addressee.name", type text}, {"addressee.cnpj", type text}, {"addressee.address.street", type text}, {"addressee.address.district", type text}, {"addressee.address.zipcode", type text}, {"addressee.address.state.uf", type text}, {"addressee.address.city.ibge", type text}, {"addressee.address.city.name", type text}, {"issued_at", type date}, {"updated_at", type datetime}}),
    #"Linhas Classificadas1" = Table.Sort(#"Tipo Alterado",{{"created_at", Order.Ascending}}),
    #"Erros Substituídos" = Table.ReplaceErrorValues(#"Linhas Classificadas1", {{"issuer.address.number", null}}),
    #"Erros Substituídos1" = Table.ReplaceErrorValues(#"Erros Substituídos", {{"addressee.address.number", null}}),
    #"Erros Removidos" = Table.RemoveRowsWithErrors(#"Erros Substituídos1"),
    #"Colocar Cada Palavra Em Maiúscula" = Table.TransformColumns(#"Erros Removidos",{{"obs", Text.Proper, type text}, {"issuer.name", Text.Proper, type text}, {"issuer.address.street", Text.Proper, type text}, {"issuer.address.complement", Text.Proper, type text}, {"issuer.address.district", Text.Proper, type text}, {"issuer.address.state.uf", Text.Proper, type text}, {"issuer.address.city.name", Text.Proper, type text}, {"issuer.address.country.name", Text.Proper, type text}, {"addressee.name", Text.Proper, type text}, {"addressee.address.street", Text.Proper, type text}, {"addressee.address.district", Text.Proper, type text}, {"addressee.address.state.uf", Text.Proper, type text}, {"addressee.address.city.name", Text.Proper, type text}, {"addressee.address.country.name", Text.Proper, type text}}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Colocar Cada Palavra Em Maiúscula",{{"created_at", type date}, {"updated_at", type date}}),
    #"Duplicatas Removidas" = Table.Distinct(#"Tipo Alterado1"),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Duplicatas Removidas",{{"created_at", "Data Criação"}, {"owner_id", "Id Proprietário"}, {"total", "Total"}, {"issued_at", "Data Publicação"}, {"updated_at", "Data Atualização"}, {"net_weight", "Peso"}, {"type", "Tipo"}, {"cte.id", "Id CTE"}, {"cte.status", "Status CTE"}, {"cte.number", "Numero CTE"}, {"issuer.id", "Id Emissão"}, {"issuer.name", "Nome Emissor"}, {"issuer.cnpj", "CNPJ Emissor"}}),
    #"Colunas Mescladas" = Table.CombineColumns(Table.TransformColumnTypes(#"Colunas Renomeadas", {{"issuer.address.number", type text}}, "pt-BR"),{"issuer.address.street", "issuer.address.number"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Endereço Emissor"),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Colunas Mescladas",{{"issuer.address.complement", "Compl. Endereço emissor"}, {"issuer.address.district", "Bairro Emissor"}, {"issuer.address.zipcode", "CEP Emissor"}, {"issuer.address.state.uf", "Estado Emissor"}, {"issuer.address.city.name", "Cidade Emissor"}, {"issuer.address.country.name", "País Emissor"}, {"addressee.id", "Id Destinatário"}, {"addressee.name", "Destinatário"}, {"addressee.cnpj", "Cnpj Destinatário"}, {"addressee.address.district", "Bairro Destinatário"}, {"addressee.address.zipcode", "CEP Destinatário"}, {"addressee.address.state.uf", "Estado Destinatário"}, {"addressee.address.city.name", "Cidade Destinatário"}, {"addressee.address.country.name", "País Destinatário"}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Colunas Renomeadas1",{"addressee.address.complement", "total_net_value", "suspended_tax", "addressee.address.country.code", "addressee.address.city.ibge", "addressee.address.state.ibge"}),
    #"Colunas Mescladas1" = Table.CombineColumns(Table.TransformColumnTypes(#"Colunas Removidas", {{"addressee.address.number", type text}}, "pt-BR"),{"addressee.address.street", "addressee.address.number"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Endereço Destinatário"),
    #"Colunas Removidas1" = Table.RemoveColumns(#"Colunas Mescladas1",{"Id Destinatário", "issuer.address.country.code", "issuer.address.city.ibge", "issuer.address.state.ibge", "Compl. Endereço emissor", "taker", "qtd_products", "input_origin", "Tipo", "ncm", "esp", "key", "weight", "Bairro Emissor", "Bairro Destinatário"}),
    #"Colunas Renomeadas2" = Table.RenameColumns(#"Colunas Removidas1",{{"Numero CTE", "ID Viagem"}, {"Nome Emissor", "Remetente"}, {"CNPJ Emissor", "CNPJ Remetente"}, {"Endereço Emissor", "Endereço Remetente"}, {"Estado Emissor", "Estado Remetente"}, {"Cidade Emissor", "Cidade Remetente"}, {"País Emissor", "País Remetente"}, {"Total", "Valor Frete"}}),
    #"Valor Substituído" = Table.ReplaceValue(#"Colunas Renomeadas2",".","",Replacer.ReplaceText,{"Peso"}),
    #"Primeiros caracteres extraídos" = Table.TransformColumns(#"Valor Substituído", {{"Peso", each Number.From(Text.Start(_, 5)), type number}}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Primeiros caracteres extraídos",{{"Valor Frete", Currency.Type}})
in
    #"Tipo Alterado2"

 

20240716_Siimp principal.pbix 1.12 MB · 0 downloads

Infelizmente ainda não deu certo, nessa imagem abaixo no power query me apresenta haver mais de 13000 linhas, mas na imagem abaixo quando carrego as informações e atualizo os dados ele me retorna apenas  640 linhas.
Estou achando que o erro pode ocorrer no codigo da função, mas não sei qual erro pode ser.

 

 

Editado Julho 16 por Eduardo_Braga
Erro na citação