UnZip in Power Query M

100%

UnZip in Power Query M

✓ Published0🌍 Public

Last edited Feb 10, 2017

Created on Feb 10, 2017

This example demonstrates a custom implementation of the UnZip algorithm entirely in Power Query M, enabling direct extraction of compressed ZIP file contents without external dependencies. It shows how binary data can be parsed and decompressed within Power BI’s data transformation environment. The code defines a binary format using `BinaryFormat.Record` to parse ZIP file headers, including signatures, compression flags, and sizes, then applies `BinaryFormat.Transform` with `Compression.Deflate` to decompress individual file entries. The result is a table of filenames and their uncompressed contents, derived from a gist by Hugoberry, showcasing low-level binary handling and list transformations in M.

AI-generated description

Similar vizzes

Loading thumbnail…

Replicating R unz function in Power Query. Based on http://sql10.blogspot.co.uk/2016/06/reading-zip-files-in-powerquery-m.html

This example demonstrates how to read a specific file from a ZIP archive directly in Power Query using a custom M function. It shows the binary-parsing logic that replicates R’s `unz` utility by manually decoding the ZIP container format. The code defines binary readers via `BinaryFormat` components, including `UnsignedInteger32` and `UnsignedInteger16` for headers, and uses `Binary.Decompress` with `Compression.Deflate` to inflate the stored file data. A `BinaryFormat.List` iterator scans the archive for the `PKZIP` signature (`0x04034B50`) to locate and extract the requested file by name. The function returns the uncompressed binary content for the matching entry.

HHugoberry

85% match

Loading thumbnail…

Dissecting ZIP file structure in Power Query M following explanation from https://users.cs.jmu.edu/buchhofp/forensics/formats/pkzip.html

This example dissects the binary structure of ZIP files using Power Query’s M language, following the PKZIP format specification from James Madison University. It visually parses a ZIP file’s local file headers, central directory entries, and end-of-central-directory records, revealing metadata like compression method, timestamps, and file names. The code leverages `BinaryFormat` combinators—such as `BinaryFormat.Record`, `BinaryFormat.Choice`, and `BinaryFormat.List`—to sequentially read and interpret byte streams, with `Binary.Decompress` handling deflate decompression. The visualization highlights how raw bytes map to structured fields, making the format’s internal layout explicit. Data originates from a sample ZIP file provided as a binary input.

HHugoberry

83% match

Loading thumbnail…

Reading a Zip file in Power Query via R

This example demonstrates how to read a compressed CSV file directly from a local ZIP archive using Power Query’s R integration. The visualization shows the result of an R script that extracts the file `R_Twitter_sample.csv` from `R_Twitter_sample.zip` and loads it into a table. The Power Query `R.Execute` function runs the R code, which relies on R’s base `read.csv` and `unz` functions to handle the archive and file parsing. The output is then returned to Power Query via the `[Name="datacsv"]` reference, making the data available for further transformation or charting. No external visualization library is used; the focus is purely on data ingestion.

HHugoberry

74% match

Loading thumbnail…

Fetching the M code from a PBIX file. Power BI Desktop.

This example demonstrates how to extract the M code from a Power BI Desktop file’s internal structure. It uses a two-step process: first, the `Unz` function decompresses the PBIX file to access the `DataMashup` folder, then decompresses the `Formulas/Section1.m` file within it. The code calls `File.Contents` on the local PBIX path and chains the `Unz` operations to reveal the raw M language source. The visualization highlights the underlying data model’s query definitions by directly reading the binary package.

HHugoberry

72% match

Loading thumbnail…

Converting a Power BI tabular connection string to Binary

This example demonstrates a Power Query (M) function that converts a Power BI tabular connection string into a binary format. It shows how to extract and decode the embedded Mashup component from the semicolon-delimited string. The function first splits the connection string using `Splitter.SplitTextByDelimiter`, isolates the last segment, and removes the "Mashup=" prefix. It then uses `Binary.FromText` to decode the remaining Base64-encoded text into binary data. The rendering relies entirely on the M language’s standard library, with no external libraries. This approach is useful for scenarios where a connection string’s embedded model needs to be accessed programmatically.

HHugoberry

67% match

Loading thumbnail…

Gist 8abd0d0a72b91b4d72ce79ac9d8ab6c4

This example demonstrates how Power Query’s `BinaryFormat.List` function reads binary data as a sequence of items, returning a list. It shows three distinct modes: reading until the data ends, reading a fixed number of items, and using a condition function to stop based on item values. The code constructs sample binary data with `#binary`, then applies `BinaryFormat.List` with `BinaryFormat.Byte` as the item format. The condition variant passes a lambda `(x) => x < 2` that continues reading while true, including the first failing item. The output lists (e.g., `{1, 2, 3}`) directly display the parsed bytes.

HHugoberry

63% match

Loading thumbnail…

Power Query HexViewer

The Power Query HexViewer displays the raw binary contents of a local text file as a structured hex dump, showing each byte’s address, offset, and hexadecimal value in a table. It reads the file `gitignore_global.txt` using Power Query’s `File.Contents` and `BinaryFormat.List`, then transforms the byte stream with `List.Transform` and `List.Positions` to compute address and offset columns. The final output uses the `#table` constructor to render the parsed data, with `Binary.ToText` formatting each byte as uppercase hex. The visualization relies solely on Power Query’s M language functions, not external JavaScript libraries.

HHugoberry

61% match

Loading thumbnail…

Extracting Power Query queries

This example shows how to extract Power Query query definitions from an embedded Power BI tabular model by reading the model’s in-memory connection string. The PowerShell script first locates the dynamic port number for the local Analysis Services instance by scanning workspace files for `msmdsrv.port.txt`. It then uses the `Invoke-ASCmd` cmdlet to query the `$SYSTEM.TMSCHEMA_DATA_SOURCES` schema, revealing the connection string. The script decodes the base64-encoded `Mashup` portion of that string, writes it to a binary ZIP archive, and uses `System.IO.Compression.ZipFile` to extract `Section1.m`, which contains the readable Power Query formulas. This approach exposes the model’s underlying data-transformation logic without any direct UI interaction.

HHugoberry

61% match