Forum Discussion

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper 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.

     

     

    • Rebecca6634's avatar
      Rebecca6634
      Frequent Visitor
      4 years ago

      Hi Ibendlin

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

       

      Thanks

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        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.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper 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"
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Rebecca6634
      Frequent Visitor
      4 years ago

      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's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      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