Forum Discussion
retrieve a data from external pages
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"
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"