cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bizzie
Frequent Visitor

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!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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"

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors