Data Mashup Cmdlets

Import and export Power Query from Microsoft Excel and Microsoft Power BI files using PowerShell Core!

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.

Related:

Installing/Upgrading

Using PowerShell Core 7+ 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.2-alpha (2020-05-01)

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 validation logic used to check new Power Query code 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. While Power BI currently uses the same data mashup storage format, external manipulation of that format in Power BI files is not officially supported by Microsoft. 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 the new, in-preview Power BI enhanced dataset metadata format.
  • 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.

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