Reading Zip files in PowerQuery / M

Being a fan of PowerBI, I recently looked for a way to read zip files directly into the Data Model, and found this blog which showed a usable technique. Inspired by the possibilities revealed in Ken's solution, but frustrated by slow performance, I set out to learn the M language and write a faster alternative.

UnzipContents

The result of these efforts is an M function - UnzipContents - that you can paste into any PowerBI / PowerQuery report. It takes the contents of a ZIP file, and returns a list of files contained therein, along with their decompressed data:



If you're not sure how to make this function available in your document, simply:

  1. Open up PowerQuery (either in Excel or in PowerBI)
  2. Create a new Blank Query.
  3. Open up the Advanced Editor  (found on the View tab in PowerBI).
  4. Copy-Paste the above code into the editor, then close the editor.
  5. In the properties window, rename the the function to UnzipContents

Usage

Using the function is fairly straight forward:
  1. Choose "New Query -> Blank Query".
  2. Open the advanced editor.
  3. Paste the following:
    let
        Source = File.Contents("[Full path to your zip file]"),
        Files = UnzipContents(Source)
    in
        Files
  4. Close the advanced editor.
  5. In the Applied Steps, click the configure cog to the right of Source.
  6. Select your Zip file.

At this point you should see a table listing every file that is contained in the zip file you selected, along with a Binary blob of the file's contents. For many file types, like CSV and Excel, you can simply click in the Binary field and PowerQuery will intelligently build all of the steps needed to expose the contents of the file.

Good luck! 


Popular posts from this blog

Power Query: Transforming YYYYMM dates (the quick way)

Easily Move SQL Tables between Filegroups