Format Power Query in Power BI (Updated)
This article has been updated in August 2023!
1. The C# script now formats Shared Expressions in addition to Partition (M - Import) object types.
2. The code is included in the Article with some extra tips and clarifications.
FORMAT POWER QUERY WITH C# SCRIPTS
…calling the powerqueryformatter.com API for a selected M partition or shared expression
WHY FORMAT YOUR CODE?
Formatting code makes it easier to read, understand and debug. For this reason, it’s a best practice to format and add comments to both DAX and Power Query (M) code in a Power BI model. For DAX, this is simple if you use the Power BI external tool Tabular Editor, which has the DAX formatter from SQLBI built-in (both for Tabular Editor 2 and 3). With the push of a button, you can format DAX in your model.
But why should you format Power Query M code, specifically? For the same reasons as why you should format DAX. It makes the code easier to read and understand, and helps both you and other developers make changes.
Goblin tip:
You should ensure that code is formatted and commented not only in a solution, but also documentation, blog posts, and articles. Improving understandability and usability of code extends to all cases where it's read.
Consider the following example of Power Query code that is unformatted without syntax highlighting, versus the code that is formatted in the second box, thereafter.
POWER QUERY FORMATTER API
A Power Query formatter from Power Pivot Insights does what its name suggests - it formats Power Query code pasted in their tool. While this is helpful, you still have to handle the code manually. However, they also have an available API. We can call this API in a Tabular Editor C# script to format Power Query, automatically.
Goblin tip:
When placing code in another tool or using an API to handle that code, make sure that you are not disclosing confidential information. In this case, the Power Query Formatter only saves information for error tracking, as described in their API documentation.
C# SCRIPTS & MACROS IN TABULAR EDITOR
One of the most powerful features in Tabular Editor, C# scripts allow programmatic access to the Tabular Model Metadata. We can use them to read, interact with and write model metadata changes with C# written inside the Tabular Editor “C# Script” window. An example use-case for Tabular Editor C# scripts are to format Power Query of a selected partition or shared expression in our model.
An example of this is shown in the following animated .gif:
Formatting a Power Query M partition or shared expression in Tabular Editor 3. The C# Script is already saved as a Macro in Tabular Editor to easily re-use it.
Copy the below C# Script to the Tabular Editor C# Script window and use it on a selected M Partition or Shared Expression to format it.
Notes & Limitations:
- Works for Table M Partitions or Shared Expressions.
- Won’t work with Power BI Desktop .pbix files since write operations to partitions are not supported from External Tools. Use .pbip files for this.
- Note that this script contacts the web service of the powerqueryformatter API. Ensure you know whether your M Expression contains sensitive information or not before using it.
- The script is provided as-is without warranty or guarantees. It has not been tested on Tabular Editor 2.
Goblin tip:
If you are new to C# Scripting or Tabular Editor, you can still benefit from this solution.
- Download and install Tabular Editor 2 (open source) or Tabular Editor 3 (start a trial)
- Connect to the model, either open in Power BI Desktop or published to PPU, Premium or Fabric capacity.
- Open the C# scripting window using the toolbar at the top. Copy and paste the C# script in here.
- Select "Save as Macro..." from the top toolbar and name it "Format Power Query". Ensure that the contexts of "Partition" and "Expression" are checked.
- Test the Macro by right-clicking a Partition or Shared Expression and selecting Macros > Format Power Query.
Goblin tip:
In Tabular Editor 3, consider using custom keyboard shortcuts and toolbars to make macros like this easier to use. This can save you a lot of time. Consider the below example, where Alt + Q formats Power Query.
TO CONCLUDE
Formatting DAX & Power Query code is an important best practice in your Tabular Models. From Tabular Editor, you can format DAX code out-of-the-box. Formatting Power Query is now something you can easily do thanks to C# scripting. This lets you automatically format M Expressions in your table partitions or shared expressions of your model when connected to model metadata (.bim or database.json) or a remote data model via XMLA Endpoint.
NEXT UP
Read about how C# scripts like this can be saved as macros and bound to keyboard shortcuts or custom toolbar buttons. With a push of a button or keyboard hotkey, you can format your Power Query code!
