Forum Discussion
Download zipped files from API
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!
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"
2 Replies
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"