Error on API Query

[eduardomaia]

I'm trying to establish an connection with the following query:

let
Origem = {1..20},
#"Convertido em Tabela" = Table.FromList(Origem, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Colunas com Nome Mudado" = Table.RenameColumns(#"Convertido em Tabela",{{"Column1", "LINHA"}}),
#"Personalizado Adicionado" = Table.AddColumn(#"Colunas com Nome Mudado", "Personalizado", each STAGE([LINHA])),
#"Expandido Personalizado" = Table.ExpandTableColumn(#"Personalizado Adicionado", "Personalizado", {"ID_EXACT_LEAD", "NM_COMPANY", "DT_REGISTER", "DT_LAST_UPDATE", "NM_ORIGIN", "NM_SDR", "NM_SALESMAN", "DE_STAGE", "DE_HEAT", "DT_AVALIATION"}, {"Personalizado.ID_EXACT_LEAD", "Personalizado.NM_COMPANY", "Personalizado.DT_REGISTER", "Personalizado.DT_LAST_UPDATE", "Personalizado.NM_ORIGIN", "Personalizado.NM_SDR", "Personalizado.NM_SALESMAN", "Personalizado.DE_STAGE", "Personalizado.DE_HEAT", "Personalizado.DT_AVALIATION"})
in
#"Expandido Personalizado"

When I'm in Query Editor it works well.. When hiting Apply I have the error 

Error on OLE DB or ODBC: [DataSource.Error] It was not possible Web.Contents obtain contents from 'https://api.spotter.exactsales.com.br/api/v2/listarlead?page=2' (500): Internal Server Error.

I just want to know if this in my query, timeout on API call, or cache from my PC... Please help

[eduardomaia]

All the steps are:

First the following blank query:

(pag as number) as table =>

let
apiUrl = "https://api.spotter.exactsales.com.br/api/v2/listarlead?page=" & Number.ToText(pag),
options = [Headers =[#"token_exact"="mytoken"]],
result = Web.Contents(apiUrl , options),
#"JSON Importado" = Json.Document(result,1252),
#"Convertido em Tabela" = Table.FromList(#"JSON Importado", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expandido Column1" = Table.ExpandRecordColumn(#"Convertido em Tabela", "Column1", {"id", "Empresa", "DtCadastro", "DtAtualizacao", "Origem", "PreVendedor", "Vendedor", "Etapas"}, {"Column1.id", "Column1.Empresa", "Column1.DtCadastro", "Column1.DtAtualizacao", "Column1.Origem", "Column1.PreVendedor", "Column1.Vendedor", "Column1.Etapas"}),
#"Expandido Column1.Origem" = Table.ExpandRecordColumn(#"Expandido Column1", "Column1.Origem", {"value"}, {"Column1.Origem.value"}),
#"Expandido Column1.PreVendedor" = Table.ExpandRecordColumn(#"Expandido Column1.Origem", "Column1.PreVendedor", {"Nome", "UltimoNome"}, {"Column1.PreVendedor.Nome", "Column1.PreVendedor.UltimoNome"}),
#"Colunas com Nome Mudado" = Table.RenameColumns(#"Expandido Column1.PreVendedor",{{"Column1.id", "ID_EXACT_LEAD"}, {"Column1.Empresa", "NM_COMPANY"}, {"Column1.DtCadastro", "DT_REGISTER"}, {"Column1.DtAtualizacao", "DT_LAST_UPDATE"}, {"Column1.Origem.value", "NM_ORIGIN"}}),
#"Colunas Intercaladas" = Table.CombineColumns(#"Colunas com Nome Mudado",{"Column1.PreVendedor.Nome", "Column1.PreVendedor.UltimoNome"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"NM_SDR"),
#"Expandido Column1.Vendedor" = Table.ExpandRecordColumn(#"Colunas Intercaladas", "Column1.Vendedor", {"Nome", "UltimoNome"}, {"Column1.Vendedor.Nome", "Column1.Vendedor.UltimoNome"}),
#"Colunas Intercaladas1" = Table.CombineColumns(#"Expandido Column1.Vendedor",{"Column1.Vendedor.Nome", "Column1.Vendedor.UltimoNome"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"NM_SALESMAN"),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Intercaladas1",{{"DT_REGISTER", type datetime}, {"DT_LAST_UPDATE", type datetime}}),
#"Colunas com Nome Mudado1" = Table.RenameColumns(#"Tipo Alterado",{{"Column1.Etapas", "DE_STAGE"}}),
#"Expandido DE_STAGE" = Table.ExpandListColumn(#"Colunas com Nome Mudado1", "DE_STAGE"),
#"Expandido DE_STAGE1" = Table.ExpandRecordColumn(#"Expandido DE_STAGE", "DE_STAGE", {"Etapa", "Qualificacao", "DtAvaliacao"}, {"DE_STAGE.Etapa", "DE_STAGE.Qualificacao", "DE_STAGE.DtAvaliacao"}),
#"Colunas com Nome Mudado2" = Table.RenameColumns(#"Expandido DE_STAGE1",{{"DE_STAGE.Qualificacao", "DE_HEAT"}, {"DE_STAGE.Etapa", "DE_STAGE"}, {"DE_STAGE.DtAvaliacao", "DT_AVALIATION"}}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas com Nome Mudado2",{{"DT_AVALIATION", type datetime}})
in
#"Tipo Alterado1"

After that I've created a new table with the range 1 to 20.

When I create the column to vinculate the initial query with the column the API returned with all the correct data.

After pressing apply and close I receive that error.

[v-shex-msft · Accepted solution]

Hi @eduardomaia,

According to error message, it seems like you got service related error message.

I think you need to check that api if they had limitations who not allow you send too many requests at same time.

Regards,

Xiaoxin Sheng

[eduardomaia]

Xiaoxin,

Thank you for your reply.

Indeed, I've simulated run the same script on ViperStresser and realized it was on the API.

Anyway, found a possibility and now I'm getting all data in a single page query.

Thanks again.

Regards,

Eduardo Maia