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.
using System.Net.Http; | |
using System.Net.Http.Headers; | |
using System.Text; | |
using Newtonsoft.Json; | |
using Newtonsoft.Json.Linq; | |
// URL of the powerqueryformatter.com API | |
string powerqueryformatterAPI = "https://m-formatter.azurewebsites.net/api/v2"; | |
// HttpClient method to initiate the API call POST method for the URL | |
HttpClient client = new HttpClient(); | |
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, powerqueryformatterAPI); | |
// Get the M Expression of the selected partition or shared expression | |
string _type = Selected.Object.ObjectTypeName; | |
string _powerquery = ""; | |
if ( _type == "Expression" ) | |
{ | |
_powerquery = Selected.Expression; | |
} | |
else if ( _type == "Partition (M - Import)" ) | |
{ | |
_powerquery = Selected.Partition.Expression; | |
} | |
else | |
{ | |
Error ("Invalid object selected. Terminating."); | |
} | |
// Serialize the request body as a JSON object | |
var requestBody = JsonConvert.SerializeObject( | |
new { | |
code = _powerquery, // Mandatory config | |
resultType = "text", // Mandatory config | |
lineWidth = 40 // Optional config | |
// alignLineCommentsToPosition = true, // Optional config | |
// includeComments = true // Optional config | |
}); | |
// Set the "Content-Type" header of the request to "application/json" and the encoding to UTF-8 | |
var content = new StringContent(requestBody, Encoding.UTF8, "application/json"); | |
content.Headers.ContentType = new MediaTypeHeaderValue("application/json"); | |
// Retrieve the response | |
var response = client.PostAsync(powerqueryformatterAPI, content).Result; | |
// If the response is successful | |
if (response.IsSuccessStatusCode) | |
{ | |
// Get the result of the response | |
var result = response.Content.ReadAsStringAsync().Result; | |
// Parse the response JSON object from the string | |
JObject data = JObject.Parse(result.ToString()); | |
// Get the formatted Power Query response | |
string formattedPowerQuery = (string)data["result"]; | |
////////////////////////////////////////////////////////////////////////// Can remove everything in this section | |
// OPTIONAL MANUAL FORMATTING // Additional formatting on top of API config | |
// Manually add a new line and comment to each step // | |
var replace = new Dictionary<string, string> // | |
{ // | |
{ "\n//", "\n\n//" }, // New line at comment | |
{ "\n #", "\n\n // Step\n #" }, // New line & comment at new standard step | |
{ "\n Source", "\n\n // Data Source\n Source" }, // New line & comment at Source step | |
{ "\n Dataflow", "\n\n // Dataflow Connection Info\n Dataflow" },// New line & comment at Dataflow step | |
{"\n Data =", "\n\n // Step\n Data ="}, // New line & comment at Data step | |
{"\n Navigation =", "\n\n // Step\n Navigation ="}, // New line & comment at Navigation step | |
{"in\n\n // Step\n #", "in\n #"}, // | |
{"\nin", "\n\n// Result\nin"} // Format final step as result | |
}; // | |
// | |
// Replace the first string in the dictionary with the second // | |
var manuallyformattedPowerQuery = replace.Aggregate( // | |
formattedPowerQuery, // | |
(before, after) => before.Replace(after.Key, after.Value)); // | |
// | |
// Replace the auto-formatted code with the manually formatted version // | |
formattedPowerQuery = manuallyformattedPowerQuery; // | |
////////////////////////////////////////////////////////////////////////// | |
// Replace the unformatted M expression with the formatted expression | |
if ( _type == "Expression" ) | |
{ | |
Selected.Expression = formattedPowerQuery; | |
} | |
else if ( _type == "Partition (M - Import)" ) | |
{ | |
Selected.Partition.Expression = formattedPowerQuery; | |
} | |
else | |
{ | |
Error( "Invalid object selected. Format either a Partition (M - Import) or Shared Expression" ); | |
} | |
// Pop-up to inform of completion | |
Info("Formatted " + Selected.Object.Name); | |
} | |
// Otherwise return an error message | |
else | |
{ | |
Info( | |
"API call unsuccessful." + | |
"\nCheck that you are selecting a partition with a valid M Expression." | |
); | |
} |
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!