Data Mashup Cmdlets

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.


Use Cases Include


      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.9-alpha (2024-04-26)


      • 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"}})
      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="" xmlns:xsi=""><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" />....


      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.
      • 0.1.9-alpha (2024-04-26)—Added a degree graceful support for non-standard compliant files.

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