topic Getting data from API with loop on text link in Developer

Getting data from API with loop on text link

Anonymous — Thu, 12 Nov 2020 15:58:23 GMT

Hi,

Sorry about my poor profficiency, I'm brazilian and starting with dev on PBI.

I created this query to get data from API. The link used contains the exclusive "data" information, that is, I can only bring one day at a time, then create the date variable, so that the query returns the full month.

It happens that some dates are out of data, returning "0" in the "detalhes"(details) field (), causing an error in the entire query because that day is null.

What can I do so that when consulting, if the value of "details" returns "0", query proceeds to the next one?

This is the variable:

(mes as text) => let Fonte = Json.Document(Web.Contents("https://blablabla.com.br/servicos/operacional/estacionamento/2/"& (mes) &"/entrada/?detalhe=true"), 20127), #"Convertido para Tabela" = Record.ToTable(Fonte), Value = #"Convertido para Tabela"{1}[Value], carro = Value[carro], detalhes = carro[detalhes], #"Convertido para Tabela1" = Table.FromList(detalhes, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Column1 Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela1", "Column1", {"codigo", "descricao", "data", "ticket", "mensal", "terminal", "setor", "tipoVeiculo", "tipoServico", "placa"}, {"Column1.codigo", "Column1.descricao", "Column1.data", "Column1.ticket", "Column1.mensal", "Column1.terminal", "Column1.setor", "Column1.tipoVeiculo", "Column1.tipoServico", "Column1.placa"}), #"Column1.terminal Expandido" = Table.ExpandRecordColumn(#"Column1 Expandido", "Column1.terminal", {"codigo", "tipo"}, {"Column1.terminal.codigo", "Column1.terminal.tipo"}), #"Column1.setor Expandido" = Table.ExpandRecordColumn(#"Column1.terminal Expandido", "Column1.setor", {"codigo", "descricao"}, {"Column1.setor.codigo", "Column1.setor.descricao"}), #"Column1.tipoVeiculo Expandido" = Table.ExpandRecordColumn(#"Column1.setor Expandido", "Column1.tipoVeiculo", {"descricao"}, {"Column1.tipoVeiculo.descricao"}), #"Column1.tipoServico Expandido" = Table.ExpandRecordColumn(#"Column1.tipoVeiculo Expandido", "Column1.tipoServico", {"descricao"}, {"Column1.tipoServico.descricao"}) in #"Column1.tipoServico Expandido"

This is the Loop:

let Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDQ0MrJUitWBcYwNkDmGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Coluna 1" = _t]), #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Coluna 1", type text}}), #"Função Personalizada Invocada" = Table.AddColumn(#"Tipo Alterado", "Carro", each Carro([Coluna 1])), #"Carro Expandido" = Table.ExpandTableColumn(#"Função Personalizada Invocada", "Carro", {"Column1.codigo", "Column1.descricao", "Column1.data", "Column1.ticket", "Column1.mensal", "Column1.terminal.codigo", "Column1.setor.codigo", "Column1.setor.descricao", "Column1.tipoVeiculo.descricao", "Column1.placa"}, {"Carro.Column1.codigo", "Carro.Column1.descricao", "Carro.Column1.data", "Carro.Column1.ticket", "Carro.Column1.mensal", "Carro.Column1.terminal.codigo", "Carro.Column1.setor.codigo", "Carro.Column1.setor.descricao", "Carro.Column1.tipoVeiculo.descricao", "Carro.Column1.placa"}) in #"Carro Expandido"

How it happens:

Anyway, tks for help.

Re: Getting data from API with loop on text link

PhilipTreacy — Fri, 13 Nov 2020 01:44:51 GMT

Hi @Anonymous

You can change your code so that if detalhes is not 0 do your usual processing but if it is 0 then return null - or return whatever want.

(mes as text) => let convert = (detalhes) => let #"Convertido para Tabela1" = Table.FromList(detalhes, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Column1 Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela1", "Column1", {"codigo", "descricao", "data", "ticket", "mensal", "terminal", "setor", "tipoVeiculo", "tipoServico", "placa"}, {"Column1.codigo", "Column1.descricao", "Column1.data", "Column1.ticket", "Column1.mensal", "Column1.terminal", "Column1.setor", "Column1.tipoVeiculo", "Column1.tipoServico", "Column1.placa"}), #"Column1.terminal Expandido" = Table.ExpandRecordColumn(#"Column1 Expandido", "Column1.terminal", {"codigo", "tipo"}, {"Column1.terminal.codigo", "Column1.terminal.tipo"}), #"Column1.setor Expandido" = Table.ExpandRecordColumn(#"Column1.terminal Expandido", "Column1.setor", {"codigo", "descricao"}, {"Column1.setor.codigo", "Column1.setor.descricao"}), #"Column1.tipoVeiculo Expandido" = Table.ExpandRecordColumn(#"Column1.setor Expandido", "Column1.tipoVeiculo", {"descricao"}, {"Column1.tipoVeiculo.descricao"}), #"Column1.tipoServico Expandido" = Table.ExpandRecordColumn(#"Column1.tipoVeiculo Expandido", "Column1.tipoServico", {"descricao"}, {"Column1.tipoServico.descricao"}) in res, Fonte = Json.Document(Web.Contents("https://blablabla.com.br/servicos/operacional/estacionamento/2/"& (mes) &"/entrada/?detalhe=true"), 20127), #"Convertido para Tabela" = Record.ToTable(Fonte), Value = #"Convertido para Tabela"{1}[Value], carro = Value[carro], detalhes = carro[detalhes], result = if detalhes <> 0 then convert(detalhes) else null in result

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Re: Getting data from API with loop on text link

Anonymous — Wed, 18 Nov 2020 11:31:33 GMT

Phil,

Its working perfectly.

Tks a lot.