Data Mashup Cmdlets | Ben Gribaudo

Import and export Power Query from Microsoft Excel (xlsx, xlsm, xlsb) and older Microsoft Power BI (pbix, pbit) files using PowerShell 7.2+!

Maybe you’d like to extract the Power Query mashups embedded in a folder full of Excel or Power BI files without opening each file one by one. Perhaps you have an M function or query you’d like to mass import into a collection of files. Then there’s version control—you’d like a mechanism to manipulate the mashups in files so that you can wire up a version control setup.

If any of these possibilities perk your interest, Data Mashup Cmdlets may be a useful tool in your Power Query toolkit.

To explore Power Query mashups and supporting details using a GUI, check out Data Mashup Explorer.
Learn about the file format used to store Power Queries in The Data Mashup Binary Stream: How Power Queries Are Stored.

Use Cases Include

Use this module to compare Power Query expressions between two files and search Power Query expressions in multiple files.
Use Visual Studio Code to edit Power Query saved in Power BI and Excel files.

Installing/Upgrading

Using PowerShell 7.2+ on Windows, install from PowerShell Gallery:

Install-Module -Name DataMashup -AllowPrerelease

Or, if you already have the module installed, to update to the latest version, run:

Update-Module DataMashup -AllowPrerelease

Latest Release: 0.1.8-alpha (2023-01-02)

Important:

Currently, Data Mashup Cmdlets are in alpha. In particular, the ability to modify content is experimental. Please make backup copies of files before you use these cmdlets to modify their contents. To empathize the inherit risk, any action that modifies a file requires that flag -Experimental be specified.
- In particular, it is likely that the syntax validation logic used to check the Power Query code you import will need refining. If you encounter cases where valid M code is rejected as invalid or invalid M code is accepted, I would be most grateful if you would email me!
Also, Microsoft only officially supports external file-level modifications of mashups in Excel files. Older Power BI used the same data mashup storage format, so in some cases this tool is able to read and manipulate Power BI files, even though Microsoft does not officially support this. If you choose to use Import-DataMashup on a Power BI file and then later contact Microsoft for help with that file, Microsoft could decline to assist.
These cmdlets do not work with (most) newer Power BI files, as they use different underlying file formats.
Currently, the Permission Bindings read from the data mashup binary stream are always assumed to be valid.

Usage Examples

Export Power Query

Outputs contents of Section1.m verbatim.

PS> Export-DataMashup -Raw SomeFile.xlsx 
section Section1;

shared People = let
    Source = #table({"FirstName", "LastName"}, {{"Tom", "Jones"}})
in
    Source;

shared Places = #table({"Location"}, {{"Washington, DC"}});

Outputs each section member (e.g. query) as an object. In turn, these objects can be filtered, sorted, compared, etc.

PS> Export-DataMashup SomeFile.xlsx
LiteralAttributes Shared Name   Expression
----------------- ------ ----   ----------
                    True People let…
                    True Places #table({"Location"}, {{"Washington, DC"}})

Export Metadata

Returns transformed JSON-formatted metadata (corresponds to Metadata.xml’s JSON tab in Data Mashup Explorer).

PS> Export-DataMashup SomeFile.xlsx -Item Metadata
{
  "General": {},
  "Formulas": {
    "Section1/People": {
      "IsPrivate": false,
      "FillEnabled": true,
      "FillObjectType": "Table",
      "FillToDataModelEnabled": false,
      "BufferNextRefresh": true,
      "ResultType": "Table",
      "NameUpdatedAfterFill": false,
      "NavigationStepName": "Navigation",
      "FillTarget": "People",
      "FilledCompleteResultToWorksheet": true,
      "AddedToDataModel": false,
...
  }
}

Outputs contents of Metadata.xml verbatim.

PS> Export-DataMashup .\SomeFile.xlsx -Item Metadata -Raw
<?xml version="1.0" encoding="utf-8"?><LocalPackageMetadataFile xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Items><Item><ItemLocation><ItemType>AllFormulas</ItemType><ItemPath /></ItemLocation><StableEntries /></Item><Item><ItemLocation><ItemType>Formula</ItemType><ItemPath>Section1/People</ItemPath></ItemLocation><StableEntries><Entry Type="IsPrivate" Value="l0" />....

Manipulation

Insert/Replace a Section Member

Only inserts if specified member does not currently exist:

PS> Import-DataMashup .\SomeFile.xlsx -InsertReplace -Action Insert -MemberName Animals -Expression '{"Cat", "Dog", "Fish" }' -Experimental

If specified member exists, replace its mashup; otherwise, inserts a new member:

PS> Import-DataMashup .\SomeFile.xlsx -InsertReplace -Action InsertOrReplace -MemberName Animals -Expression '{"Cat", "Dog", "Fish" }' -Experimental

if specified member exists, replace its mashup; otherwise, makes no changes:

PS> Import-DataMashup .\SomeFile.xlsx -InsertReplace -Action Replace -MemberName Animals -Expression '{"Cat", "Dog", "Fish" }' -Experimental

Rename a Section Member

Changes the name of a section member (e.g. query). Below, Places is renamed Locations.

PS> Import-DataMashup .\SomeFile.xlsx -Rename -MemberName Places -RenameToMemberName Locations -Experimental

Remove a Section Member

Removes a section member (e.g. query). Below, People is deleted.

PS> Import-DataMashup .\SomeFile.xlsx -Remove -MemberName People -Experimental

Replace Raw Mashup

Replaces entire mashup with the specified.

PS> Import-DataMashup .\SomeFile.xlsx -Raw "section Section1; shared A = true;" -Experimental

Microsoft Excel & Power BI both only support a single section, named “Section1”, and seem to expect that all section members are shared. While neither of these are mandated by the Power Query language specification, Data Mashup Cmdlets enforces these rules to align with Excel and Power BI.

Support/Feature Requests/Ideas

If these cmdlets misbehave on you, please drop me an email. If you have an idea for a feature or improvement, you’re welcome to add your suggestion below as a comment or email.

Change Log

0.1.0-alpha (2020-04-14)—Initial release
0.1.1-alpha (2020-04-20)—Minor update addressing issue where certain member names caused problems
0.1.2-alpha (2020-05-01)—Minor update improving Power Query parsing.
0.1.3-alpha (2020-06-08)—Minor update improving Power Query parsing.
0.1.4-alpha (2020-06-23)—Minor update addressing issues related to comments at the start and end of expressions.
0.1.5-alpha (2020-08-12)—Added support for non-shared section members: non-shared members are now allowed when replacing raw mashups (Import-DataMashup -Raw); replacing a non-shared section member (e.g. Import-DataMashup -InsertReplace -Action Replace or -Action InsertReplace) will preserve that member’s non-shared status. Added support for Excel macro-enabled (xlsm) workbooks.
0.1.6-alpha (2020-08-25)—Minor update improving Power Query parsing.
0.1.7-alpha (2020-09-17)—Added support for Excel binary (xlsb) workbooks.
0.1.8-alpha (2023-01-02)—Revised to reflect recent M grammar changes.

For notification of new versions, follow @bgribaudo on Twitter.

Related