Skip to main content
SimilarExport ZIP
100%

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

✓ Published0🌍 Public
HHugoberry
Last edited Jul 17, 2017
Created on Jul 17, 2017

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.

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
72% match
Loading thumbnail…

Displays all of the Power Query M scripts from the opened instances of Power BI. (ignore the ts extension, this is an M script:)

This visualization displays every Power Query M script extracted from the currently open Power BI Desktop instances. It first locates the active Analysis Services workspace ports by scanning recently accessed files, then retrieves each connection string, decodes the embedded Mashup binary data, and decompresses the ZIP archive to isolate the “Formulas/Section1.m” file. The resulting M code is shown line by line, using the `Folder.Files`, `AnalysisServices.Database`, and `Binary.Decompress` APIs to reconstruct the scripts.

HHugoberry
70% match
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
69% 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
68% match
Loading thumbnail…

PBIX deconstruction of Power BI files

This example deconstructs a Power BI (.PBIX) file by parsing its internal JSON layout and data transformation metadata, then reconstructing the report page as an interactive scatter plot. The visualization parses the file’s sections, visual containers, and query definitions, extracting coordinates, dimensions, and the visual’s configuration (including projections and aggregation) to determine plot placement. The scatter plot maps the X and Y axes to count aggregations of fields from Query1, with categories and series driven by the Z field and its clustered groups; the accompanying structure mirrors the source’s raw layout JSON, highlighting how Power BI’s internal model maps to rendered elements. The result is a deconstructed view of a Power BI report, illustrating the relationship between the report's serialized metadata and the visual output.

HHugoberry
68% 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
67% 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
66% match
Loading thumbnail…

Gist 6bae1006f0a86eb81bd0914e96710170

This example demonstrates how to inspect and export metadata from Power BI files on macOS without Power BI Desktop. Using the Python library PBIXRay, it loads a `.pbix` file with `on_disk=True` for memory-mapped access, then exports model schema, DAX measures, calculated columns, relationships, and Power Query expressions to CSV files. The code also shows extracting stored table data via `model.get_table()` and pandas DataFrames, with a separate example using `iter_table()` for handling larger models in chunks.

HHugoberry
64% match