Skip to main content
SimilarExport ZIP
100%

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

✓ Published0🌍 Public
HHugoberry
Last edited Mar 20, 2017
Created on Mar 20, 2017

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.

AI-generated description

Similar vizzes

Loading thumbnail…

UnZip in Power Query M

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.

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
79% 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
77% 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
69% 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…

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
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…

DEC2BIN or HEX2BIN in Power Query M

This example demonstrates a Power Query M function that converts a decimal number to its binary string representation, implementing the equivalent of Excel’s DEC2BIN and HEX2BIN operations. The function, `Number.DEC2BIN`, takes a numeric input and uses `List.Generate` to iteratively extract bits via `Number.BitwiseAnd` and `Number.BitwiseShiftRight`. Each iteration prepends the least significant bit to an accumulating string, while the loop continues until the remainder becomes zero. The code relies on M’s native `Number.ToText` for bit conversion and explicitly shows the bitwise arithmetic and list-generation logic, with no external libraries or data sources involved.

HHugoberry
59% match