Forum Discussion
retrieve a data from external pages
Given a column with the name of the countries, is there a way to retrive (without importing tables, maybe with a function that connects to an external service) the code alpha3 of the states and save them in another costum column?
Anonymous you can use this approach to get the raw CSV from the GitHub website without import the CSV into the Data Model, or including is a separate query. The trick is to Buffer the table to prevent the query running multiple times against Github. In the example there are 2 ways to lookup the country to find the Alpha-3 code: (1) a table filter approach and (2) a table join approach.
let Source = Web.Contents("https://gist.github.com/radcliff/f09c0f88344a7fcef373/raw/2753c482ad091c54b1822288ad2e4811c021d8ec/wikipedia-iso-country-codes.csv"), #"Open Csv" = Csv.Document(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Open Csv", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"English short name lower case", "Name"}}), #"Buffer Table" = #"Renamed Columns", #"Your Data Table" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7IzElVitUBsvJz8nOTMhPBHPfUotzEvEql2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t]), #"Lookupup Approach" = Table.AddColumn(#"Your Data Table", "Country Code", each #"Buffer Table"{[Name=_[Country]]}[#"Alpha-3 code"]), #"Join Approach" = Table.NestedJoin(#"Lookupup Approach", {"Country"}, #"Buffer Table", {"Name"}, "Country Codes", JoinKind.LeftOuter), #"Expand Join Table" = Table.ExpandTableColumn(#"Join Approach", "Country Codes", {"Alpha-3 code"}, {"Alpha-3 code"}) in #"Expand Join Table"
5 Replies
Hi Anonymous - I trying to understand the reason for avoiding the Import of the table. The Country/State lookup table can be added to Power Query without the "Enable Load" option unchecked. This means the Query will not be loaded to the data load. You can still use the unloaded query in other queries within functions or as Merge Query (joins).
Also, it will be better to pull the Country/State table once into Power Query using Table.Buffer rather that searching a externally linked source row by row.
Thanks Anonymous - you can create a function that performs a Web call for each Country/State value. How you do this will depend on the External data source. For example, if it is an API call, then you can pass the value in the relativepath, then parse the results. If the data source provides a CSV list, then you will open the list, find the value. or finally, it is located on a Web page, power query will scan the html table or page for the required value.
Do you have a data source in mind?
But please note there will be a performance hit because in each row the call will be performed to the external data source. This introduces a lot of latency. So you are bettter off calling all the data from the Data Source only once, and then referencing that in-memory table for the row by row function.I found this site that would work to me but i dont understand where to put the "code" and what kind of code should i write since i this case power query should scan the html table for the reuired value
https://www.iban.com/country-codes
Alternative if it is easier also the csv file here https://gist.github.com/radcliff/f09c0f88344a7fcef373 could be considered as a solution but like before i dont know what code and where write it
Anonymous you can use this approach to get the raw CSV from the GitHub website without import the CSV into the Data Model, or including is a separate query. The trick is to Buffer the table to prevent the query running multiple times against Github. In the example there are 2 ways to lookup the country to find the Alpha-3 code: (1) a table filter approach and (2) a table join approach.
let Source = Web.Contents("https://gist.github.com/radcliff/f09c0f88344a7fcef373/raw/2753c482ad091c54b1822288ad2e4811c021d8ec/wikipedia-iso-country-codes.csv"), #"Open Csv" = Csv.Document(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Open Csv", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"English short name lower case", "Name"}}), #"Buffer Table" = #"Renamed Columns", #"Your Data Table" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7IzElVitUBsvJz8nOTMhPBHPfUotzEvEql2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t]), #"Lookupup Approach" = Table.AddColumn(#"Your Data Table", "Country Code", each #"Buffer Table"{[Name=_[Country]]}[#"Alpha-3 code"]), #"Join Approach" = Table.NestedJoin(#"Lookupup Approach", {"Country"}, #"Buffer Table", {"Name"}, "Country Codes", JoinKind.LeftOuter), #"Expand Join Table" = Table.ExpandTableColumn(#"Join Approach", "Country Codes", {"Alpha-3 code"}, {"Alpha-3 code"}) in #"Expand Join Table"