Tag Archives: Data Mashup Cmdlets

Editing Report/Spreadsheet Mashups in VSCode

• , , , , ,

Data Mashup Cmdlets import and export Power Query from most Microsoft Power BI and Microsoft Excel files; however, they don’t include a GUI editor. Microsoft’s new Power Query / M Language extension for Visual Studio Code provides a graphical editing experience for mashups, with syntax highlighting, validation, and IntelliSense, but doesn’t read or write PBIX, PBIT or XLSX files.

What if the two could be married? Imagine…if this were possible, you could use VSCode to externally edit the Power Query code saved in your Power BI and Excel files….

Now, stop imagining. Try Edit-DataMashup!

Screenshot showing VSCode being used to edit Power Query from a report file
Continue reading →

Searching Power Query in Multiple Files

• , , , ,

You’re looking for all mashups that touch column “LegacyAmount”…or maybe the task is to find all files whose Power Query uses variable “CalculatedDiscount”…or perhaps you’re trying to locate any M code referencing server names that match the regular expression pattern “SomeServer(0[135]|[2-6]\d)“. Ultimately, you need to search the Power Query mashups in an entire directory (or directory tree) of Microsoft Excel and Microsoft Power BI files. You could open files one at a time and check each by hand…ouch! Is there an easier way to quickly search Power Query across multiple files?

Try Search-DataMashup!

Output of Search-DataMashup '.\' '*Source*'
Continue reading →

Comparing M Code Between Files

• , , ,

You’d like to compare the Power Query mashups contained in two Microsoft Excel and/or Microsoft Power BI files. Opening Query Editor for each and hand-comparing the queries one at a time quickly becomes tediously painful with all but the smallest of query sets. An easy way to find the differences between mashups in the files would be most helpful.

How about something as simple as:

Compare-DataMashup SomeFile.xlsx OtherFile.pbix
Screenshot showing the comparison of a query between two files: ====== Query: Employee ====== --- File1 +++ File2 @@ -1,4 +1,6 @@ let - Source = #table(null, {{"Tom", 15, true}}) + Source = #table(null, {{"Tom", 15, true}}), + #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Currency.Type}, {"Column3", type logical}}), + #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}, {"Column2", "Rate"}, {"Column3", "Active"}}) in - Source + #"Renamed Columns"
Continue reading →