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"
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"