Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am trying to extract data from an API, but the files are zipped, and I can't get Power Query to properly decode the underlying CSV file. I have tried various iterations of Mark White's BI Blog solution (which I have simply copied & pasted, I am completely ignorant of how it works), but I run into "An error occurred in the ‘’ query. Expression.Error: We cannot convert a value of type Binary to type Text."
Is there a way to pull a zipped file directly from an online data source? An example file is:
https://www.petrinex.gov.ab.ca/publicdata/api/files/AB/VOL/2021-11/CSV
Thanks for any help you can provide!
Solved! Go to Solution.
Using @artemus Unzip code from here, I managed to get this to load as follows:
let
Source = fn_Unzip(Web.Contents("https://www.petrinex.gov.ab.ca/publicdata/api/files/AB/VOL/2021-11/CSV")),
Content = fn_Unzip(Source{0}[Content]){0}[Content],
#"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
#"Promoted Headers"
Using @artemus Unzip code from here, I managed to get this to load as follows:
let
Source = fn_Unzip(Web.Contents("https://www.petrinex.gov.ab.ca/publicdata/api/files/AB/VOL/2021-11/CSV")),
Content = fn_Unzip(Source{0}[Content]){0}[Content],
#"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
#"Promoted Headers"
Yes, works perfectly!! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.