Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
karinafreitass
Helper III
Helper III

Search list of records

 

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: 

Screenshot (310).png

 

I would like to use the following list for consultation:

Screenshot (309).png

 

What function could I query for each record? I am using the "record.field" function that returns only 1 recordScreenshot (308).png

 

Thanks so much.

Karina

1 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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
Super User

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

br-cit.png

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


@PhilipTreacyThaniks.

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

Screenshot (314).png

Do you have any idea what's going on?

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

Karina

 

 

PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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)

Screenshot (311).png

 

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

Screenshot (312).png

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

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.