Opening zipped Excel file from web url

[Rebecca6634]

I'd like to import the zipped Excel file into Power BI from this website:

https://www.ons.gov.uk/peoplepopulationandcommunity/housing/datasets/hpssadataset2medianhousepriceby...

I've tried a couple of suggestions from the form e.g Reading Zip files in PowerQuery / M (sql10.blogspot.com) but I haven't been able to find a working solution. Can anyone help?

Many thanks,

Rebecca

[Daquinor987]

Hey there,

 Im having a similar issue with this page:

https://www.osinergmin.gob.pe/seccion/centro_documental/hidrocarburos/SCOP/SCOP-DOCS/2023/Registro-p...

need a little help

[lbendlin]

PLease qualify what you mean by "issue".  Where are you stuck?

Here is the boilerplate code that works for me

let
    Source = Unzip(Binary.Buffer(Web.Contents(URL))),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"FileName", "Content"}, {"FileName", "Content"})
in
    #"Expanded Column1"

You can get the Unzip function from here: Solved: Re: How to connect Azure DevOps REST API in to pow... - Microsoft Fabric Community

[v-eqin-msft]

Hi @Rebecca6634 ,

I have tried to firstly download the .zip file and followed the tutorial video

1. Create a Blank query, paste the code in the blog you provided.

2. Get data from Folder to connect the .zip file. Then add a Invoke Custom column:

3. After removing other columns, expand the "Binary" type column, such error occurs as @lbendlin mentioned.

So finally you need to unzip the file and Get data from Excel to get tables instead.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rebecca6634]

Hi Eyelyn

Thanks for your reply. The zip file on the website updates regularly so ideally I want to connect and use the zip file directly from the website, without having to manually download a local copy to my machine. My report uses an automated refresh to stay up to date with the latest data so I need to avoid the step which requires me manually download the file.

Please could you advise if there is a way to do this?

Many thanks

[lbendlin]

It's straightforward.  Save the function that you are referencing in your post as a new query, call it "Unzip", and then add your own query:

let
    Source = Web.Contents("https://www.ons.gov.uk/file?uri=%2fpeoplepopulationandcommunity%2fhousing%2fdatasets%2fhpssadataset2medianhousepricebymsoaquarterlyrollingyear%2fcurrent/hpssadataset2medianpricepaidbymsoa.zip"),
    ExcelFile = Unzip(Source),
    Content = ExcelFile{0}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(Content)
in
    #"Imported Excel Workbook"

The big, big problem here is that it is an .XLS file which is an ancient format that is not supported on the Power BI service.  You can read it on the Power BI desktop after installing the equally ancient support library but then you are stuck.

[martim224]

I am having a similar issue but zip file contains a Text Document.  Zip file is at https://www.accessdata.fda.gov/MAUDE/ftparea/mdrfoi.zip.  What would the modified text look like?  Thank you.

[lbendlin]

let
    Source = Unzip(Web.Contents("https://www.accessdata.fda.gov/MAUDE/ftparea/mdrfoi.zip")),
    #"Imported CSV" = Csv.Document(Source{0}[Content],[Delimiter="|", Columns=82, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
    #"Promoted Headers"

[Rebecca6634]

Hi Ibendlin

So do you think it is not possible to use the file in Power BI?

Thanks

[lbendlin]

You can use it on Power BI Desktop (after installing the required library).  You will not be able to do scheduled refresh on the Power BI Service.