Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
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.
Do you have any idea what's going on?
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
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
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
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)
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |