Skip to content

Instantly share code, notes, and snippets.

@data-goblin

data-goblin/format-m-expression.csx

Created March 9, 2023 20:01
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save data-goblin/84376c9dcebb9fcd4cbbbc120939e813 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save data-goblin/84376c9dcebb9fcd4cbbbc120939e813 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. data-goblin renamed this gist Mar 9, 2023. 1 changed file with 0 additions and 0 deletions.
    0 format-m-expression → format-m-expression.csx
    View file
    File renamed without changes.
  2. data-goblin created this gist Mar 9, 2023.
    84 changes: 84 additions & 0 deletions format-m-expression
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,84 @@
    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
    string _expression = Selected.Expression;

    // Serialize the request body as a JSON object
    var requestBody = JsonConvert.SerializeObject(
    new {
    code = _expression,
    resultType = "text",
    lineWidth = 40,
    alignLineCommentsToPosition = true,
    includeComments = true
    });

    // 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"];

    ///////////////////////////////////////////////////////////////////////
    // OPTIONAL MANUAL FORMATTING
    // Manually add a new line and comment to each step
    var replace = new Dictionary<string, string>
    {
    { " //", "\n\n//" },
    { "\n #", "\n\n // Step\n #" },
    { "\n Source", "\n\n // Data Source\n Source" },
    { "\n Dataflow", "\n\n // Dataflow Connection Info\n Dataflow" },
    {"\n Data =", "\n\n // Step\n Data ="},
    {"\n Navigation =", "\n\n // Step\n Navigation ="},
    {"in\n\n // Step\n #", "in\n #"},
    {"\nin", "\n\n// Result\nin"}
    };

    // 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
    Selected.Expression = formattedPowerQuery;

    // Pop-up to inform of completion
    Info("Formatted " + Model.Expressions.Where(e => e.Expression == Selected.Expression).ToList()[0].Name;
    }

    // Otherwise return an error message
    else
    {
    Info(
    "API call unsuccessful." +
    "\nCheck that you are selecting a Shared Expression with a valid M Expression."
    );
    }