The Power Query Trap: How to Build a Bulletproof Blacklist for Your Automated Data Dictionary

If you have ever tried to automate your Power BI documentation by parsing the .pbip or .bim metadata, you know the initial thrill. You write a script to extract your tables, relationships, and M-code, and suddenly, your entire model architecture appears on the canvas.

But then you look closer at your newly generated Data Dictionary, and you see the clutter:

• Transform Sample File
• Parameter1
• Transform File (2)

If a developer used the „Get Data from Folder” wizard, Power Query silently generated a mountain of helper artifacts—hidden functions, sample files, and system parameters. You don’t want these in a client-facing architectural document. They are junk.

The immediate instinct is to build a hardcoded blacklist. But this is exactly where most automated documentation scripts fail.

The Trap of Name-Based Filtering

A junior developer will look at that list of junk and write a quick Power Query filter: Table.SelectRows(Source, each not Text.StartsWith([TableName], "Parameter"))

This is a massive trap for two reasons:

1. Collateral Damage: What if you or your team creates a perfectly legitimate, highly important parameter and names it Parameter_SQL_Server? Your script just deleted it from the documentation.
2. The Multilingual Nightmare: If you distribute your template or work with international clients, the Power Query wizard translates those helper files. In Polish, it’s Przekształć plik. In German, it’s Beispieldatei transformieren. Your English-hardcoded blacklist will instantly fail, and the junk will flood back into the report.

The „Surgical” Blacklist: Filtering by M-Code Signatures

To build an Enterprise-grade parser, you have to stop looking at the table names and start looking at the DNA of the code itself.

Even if the Power BI interface language changes, the underlying M-code engine relies on specific, unalterable structural signatures to handle these system files. Instead of filtering by what the object is called, we filter by how it is constructed.

For example, when Power Query creates an automatic folder parameter, it injects a specific metadata tag that a human developer would almost never write manually: BinaryIdentifier=.

Similarly, the sample files generated by the wizard always contain a specific reference to the binary content of the first row: {0}[Content].

By inspecting the raw M-code string of the extracted objects, we can build a truly surgical blacklist:

FilteredExpressions = Table.SelectRows(ExpandedExpressions, each 
    let 
        mCodeStr = [RawExpression]
    in
        // 1. Block System Parameters (catches the hidden metadata tag)
        not Text.Contains(mCodeStr, "BinaryIdentifier=") and              
        
        // 2. Block System Transform Functions (catches the binary function signature)
        not (Text.Contains(mCodeStr, "as binary) =>") and Text.Contains(mCodeStr, "let")) and

        // 3. Block Sample Files (catches the specific first-row content reference)
        not Text.Contains(mCodeStr, "{0}[Content]")
)

With this logic, you can name your own parameter Parameter1 and the engine will let it pass into your Data Dictionary, because it lacks the BinaryIdentifier= tag. Meanwhile, every single piece of folder-wizard junk is silently blocked, regardless of what language the Power BI Desktop client is using.

Build Systems, Not Just Scripts

Building robust, edge-case-proof parsers is the difference between a brittle script and a reliable tool. However, testing and maintaining these M-code parsers across different Power BI updates requires significant engineering bandwidth.

If you want to skip the trial and error, this surgical M-code parser is baked natively into the extraction engine of ModelLens, developed under theBItoolbox.

ModelLens securely scans your local .pbip files, intelligently filters out the system-generated noise, and outputs a crystal-clear, client-ready Data Dictionary—so you can focus on building analytics, not cleaning up metadata.

Explore the complete ModelLens documentation framework here:
https://thebitoolbox.gumroad.com/l/modellens