Getting data from API with loop on text link

[Anonymous]

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.

[Anonymous]

Phil, 

Its working perfectly. 

Tks a lot.

[PhilipTreacy · Accepted solution]

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.