Forum Discussion

Rebecca6634's avatar
Rebecca6634
Frequent Visitor
4 years ago

Opening zipped Excel file from web url

I'd like to import the zipped Excel file into Power BI from this website:   https://www.ons.gov.uk/peoplepopulationandcommunity/housing/datasets/hpssadataset2medianhousepricebymsoaquarterlyrollingy...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

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.

 

 

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    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"