Tag Archives: Data Mashup Cmdlets

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 @@
-    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"}})
-    Source
+    #"Renamed Columns"
Continue reading