Released today: 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.
Quick examples:
// Output the various members (e.g. queries) in a file
PS> Export-DataMashup SomeFile.xlsx
LiteralAttributes Shared Name Expression
----------------- ------ ---- ----------
True People let…
True Places #table({"Location"}, {{"Washington, DC"}})
// Add a new member (e.g. query)
PS> Import-DataMashup .\SomeFile.xlsx -InsertReplace -Action Insert -MemberName Animals -Expression '{"Cat", "Dog", "Fish" }' -Experimental
// Rename an existing member
PS> Import-DataMashup .\SomeFile.xlsx -Rename -MemberName Places -RenameToMemberName Locations -Experimental
// Delete a member
PS> Import-DataMashup .\SomeFile.xlsx -Remove -MemberName People -Experimental
For more details, installation instructions and usage examples, head over to Data Mashup Cmdlets.
Also, today, Data Mashup Explorer received a minor update—several additional JSON metadata transform rules were added.
Ben, I’m a PowerShell novice. I pasted the command from https://www.powershellgallery.com/packages/DataMashup/0.1.7-alpha , but it showed the error: “Install-Module : A parameter cannot be found that matches parameter name ‘AllowPrerelease’.” So I changed the command to: Install-Module -Name DataMashup. It showed the error: “PackageManagement\Install-Package : No match was found for the specified search criteria and module name ‘DataMashup’.”
I do have access to the repository PSGallery. However, $PSVersionTable returns in Windows PowerShell:
PSVersion 5.1.19041.1682
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
BuildVersion 10.0.19041.1682
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
How can I install with this version? Thanks.
Is it an option for you to install PowerShell Core (7+)? Unfortunately, DataMashup isn’t supported on PowerShell 5.