Search list of records

[karinafreitass]

Hello people!

Would anyone know how to tell me how to consult a list on the web? Please!

The query will be made through this link that I brought to the power query: 

I would like to use the following list for consultation:

What function could I query for each record? I am using the "record.field" function that returns only 1 record

Thanks so much.

Karina

[PhilipTreacy]

HI @karinafreitass 

Download a sample PBIX file with this code

One way to do this is to add a Custom Column in your Zip Codes table.  For each row call the website and pass in the zip code.  Here's the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjGyMDIwMFCK1YlWMjQyMTOAcUzNTSzhHAsLQ0NTGMfIxMgEIhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cep Destino" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Xml.Tables(Web.Contents("www.cep.republicavirtual.com.br/web_cep.php?cep=" & Text.From([Cep Destino])), null, 65001)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"uf", "cidade"}, {"Custom.uf", "Custom.cidade"})
in
    #"Expanded Custom"

resulting in this

Someof the characters aren'tdisplaying properly in my PBI as I'm using English. They shoud be fine if you are using Portuguese/Brazil as your locale/region.

regards

Phil

[karinafreitass]

@PhilipTreacyThaniks.

I'm using it in Portuguese, but it is repeating the postal codes and bringing me results other than the real one.

Do you have any idea what's going on?

[PhilipTreacy · Accepted solution]

Hi @karinafreitass 

Change the encoding value in the Xml.Tables call from 65001 to 65000.

So it should be like this

#"Added Custom" = Table.AddColumn(Source, "Custom", each Xml.Tables(Web.Contents("www.cep.republicavirtual.com.br/web_cep.php?cep=" & Text.From([Cep Destino])), null, 65000)),

When you say it is repeating the postal codes, do you mean the column is still there?  Just delete that column if you don't want it.  Same for other columns you do not want in the final table.

Here's an updated PBIX file with these changes

Regards

Phil

[karinafreitass]

Now it worked perfectly. I deleted the column with repeated values. Thanks so much for the help @PhilipTreacy 

Karina

[PhilipTreacy]

Hi @karinafreitass 

I'm not entirely clear on what it is you want to do.or which piece f code you posted prduces that list (single column)

If you want to access a value in that single column you can do so like this

Value = Fonte[Cep Destino]{0}

Which will give you the first value in the Cep Destino column of a table (Step Name) called Fonte

Regards

Phil

[karinafreitass]

Hi @PhilipTreacy Thank you so much for returning.

I will try to explain it better.

I have a table with zip code records (postal address code)

I created another table with a web source that will search for each record in the CEP table and return me to the city

Which parameter should I use so that each record in the "cep destino" is consulted in the web_cep table?

I hope I explained it better.

Thanks