stepsAI 02

Power Query – Optimization with functions and variables

By Simon-Pierre Morin, eng. – March 2024
(intermediae level)

Following the Step Optimisation article, let’s enhance our work methods with Power Query to be more efficient and to have more readable and maintainable queries. The mentioned examples within this articles follow the code obtained from “Reducing Steps“. Keep in mind that the read order of these article does not matter and that the example file contains all modifications from the whole optimization sub-serie.

Starting from the code of the previous article, let’s improve the maintainability and readability with:

  • parameters,
  • constants,
  • custom functions,
  • records
  • simulate switch-case in replacement of if-else.

Parameters

Parameters and constants are global named values in Power Query that can be called from anywhere in the queries. From the Power Query editor, it is quite simple to manage the parameters without using the Advanced Editor. To do it, simply go in the “Home” ribbon, then under the “Parameter” group, click on “Manage Parameters” (sorry, screenshots were made using a French Power Query Editor).

image 2
image 3

In the query editor, parameters will look like this (“valeur actuelle” means “current value”):

image 4

And in the advanced editor, parameters look like this:

"current value" meta [IsParameterQuery=true, List={"value1", "value2", ...}, DefaultValue="default", Type="...", IsParameterQueryRequired=true]

where only the meta IsParameterQuery, Type and IsParameterQueryRequired are required.

Constants

Constants are like parameters except they cannot be modified by the user after being published. In their declare statement, we only keep the Type metadata.

"value" meta [Type="..."]

In the visual query editor, constant will look like this:

image 5

For beginners, I suggest to create a constant as if it was a parameter to ensure the syntaxe then to alter it to make it constant. After a while, you will probably be able to create constant manually.

It is also possible to use global variables (queries defining only a value). The system will then consider the “Any” type which will make it accept literally any value.

image 6

Meanwhile, it is a better practice to use global constant instead of global variables since we are enforcing a type. Since both parameters and constants are going to be called in many other queries, forcing a type ensure data integrity and avoid errors.

Custom Functions

It may happen that some intermediate steps are only informative addition. It is also possible that we need to reuse part of that code in other queries. In the base example, it was the case for Icon (that prepare an intermediate result) and addTitlePlus (that do the formatting) steps. Even worse than having useless steps, there was an extra step just to get rid of the intermediate results. Previously, we were using sub steps for that formatting, but we could gain even more performance and maintainability by creating a function. That function should compute the icon required by addTitlePlus.

This function could be setup as a functional request instead of the current query to be called from anywhere. In my example, I put it in a utility function record, but this way of organizing is optional.

getIcon = (wit as text) as text =>
    if wit = "Epic" then "👑"
    else if wit = "Feature" then "🏆"
    else if wit = "User Story" then "📘"
    else if wit = "Bug" then "🐞"
    else if wit = "Action Plan" then "📑"
    else if wit = "Issue" then "🚩"
    else if wit = "Task" then "📋"
    else if wit = "Test Case" then "📃"
    else if wit = "Test Plan" then "🗃"
    else if wit = "Test Suite" then "📂"
    else "🚧"
,

In the step to add the formatted title column, we then just have to call that new function.

addTitlePlus = Table.AddColumn(
    url,
    "Title+",
    each utils[getIcon]([WorkItemType]) & " " & [Title],
    type text
)

Or even, if the original Title column is never used without an icon, instead of adding one column, we could simply change the original one:

formatTitle =  Table.ReplaceValue(
    url,
    each [Title],
    each utils[getIcon]([WorkItemType]) & " " & [Title],
    Replacer.ReplaceText,
    {"Title"}
)

The function record is optional. This is only a personal preference to have a single query named “utils” where I got all my utility functions rather then having one query per function. One or the other has absolutely no impact on performances whatsoever. We will see later that records can brings other benefit, but has it is for now, just go with your personal preferences. There is no point to use one instead of the other. Just keep in mind that for the examples, I put my functions in a record.

One of the main advantages for using a utility function, other than saving steps and be reusable, is on maintainability perspective. Let’s imagine that getIcon function is used in five different queries throughout the project. If, after a while, we want to add a new type (for exemple “Documentation”) which is not yet present. All we need to do it to edit the utility function to add said type and affect its new icon. Also, we are not dependent of the column names anymore since we are passing their content as parameter. This allows to have the same behavior disconnected from the container. We could even go further by creating a format function (taking the title and the type in parameter) to uniformize the title format. This function could be used for column creation or modification:

formatTitle = (wit as text, title as text) as text => getIcon(wit) & " " & title,

For this example, I reused the already existing getIcon, but it could have been only one function.

The invoke is done as before:

formatTitle = Table.ReplaceValue(
    url,
    each [Title],
    each utils[formatTitle]( [WorkItemType], [Title] ),
    Replacer.ReplaceText,
    {"Title"}
)

And here is what the utility function query could look like. I also add a URL format function that take the “org” global parameter or constant:

let

    getIcon = (wit as text) as text =>
        if wit = "Epic" then "👑"
        else if wit = "Feature" then "🏆"
        else if wit = "User Story" then "📘"
        else if wit = "Bug" then "🐞"
        else if wit = "Action Plan" then "📑"
        else if wit = "Issue" then "🚩"
        else if wit = "Task" then "📋"
        else if wit = "Test Case" then "📃"
        else if wit = "Test Plan" then "🗃"
        else if wit = "Test Suite" then "📂"
        else "🚧",

    formatTitle = (wit as text, title as text) as text => getIcon(wit) & " " & title,

    formatURL = (proj as text, id as number) as text =>
        "https://dev.azure.com/"& org & "/" & proj &"/_workitems/edit/"&Text.From(id),

    utils = [
        getIcon = getIcon,
        formatTitle = formatTitle,
        formatURL = formatURL
    ]

in utils

This technique is only usefull if we have to do these operations more than once. For maintenance, performance and readability, they are still beneficials.

The Power of Records

There are still room from improvement in the current solution. The if…elseif…else structure is a loss of efficiency, but many use this since the switch…case structure does not exist in Power Query. Hopefully, there is a little trick that exist to be as efficient as a switch. It’s to use a record.

Let’s create a record in our utility query to define the work item types as the key and their associated icon as value.

iconSet = [
   Epic =  "👑",
   Feature = "🏆",
   #"User Story" = "📘",
   Bug = "🐞",
   #"Action Plan" = "📑",
   Document = "🖺",
   Issue = "🚩",
   Task = "📋",
   #"Test Case" = "📃",
   #"Test Plan" = "🗃",
   #"Test Suite" = "📂"
]

Notice that keys are variable names which need to conform their conventions. You must put any key that contains special characters (spaces, hyphens, foreign language characters) between quotes preceding by the pound sign (#) as we can see with #”User Story”, #”Action Plan” and test related items.

By doing so, it is then possible ot change the getIcon function as follow:

getIcon = (wit as text) as text => Record.FieldOrDefault(iconSet, wit, "🚧")

where the default value is writing inside the FieldIrDefault native function rather then in a conditional structure. This function is basically asking to return the value of the wit (work item type) key from our iconSet record. This operation is almost instant since it is simply a reference instead of taking a couple of extra miliseconds for each test condition, which could become extremely costly for complex verifications.

It is the same for any other type of operation requiring a value based condition. Since the records are a key-value pair, where the key must be a string for which the value is of type ANY, it is possible to put virtually anything (a primitive value, a function, a list, another record, even a table). Keep in mind that records are by far the most versatile structure of Power Query that can by itself, when used wisely, simplify a complex problem.

Conclusion – Current Solution

There are several methods to optimize Power Query queries. By putting all together what was seen in the current post with the previous one, we get the solution bellow. As a little bonus, I am adding the local configuration variables as a local record. There is no real advantage to do so except that the configuration is within a single step in the editor with all the required variable for the query.

Global Parameters

org

"KuriosIT" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

odataversion

"v4.0-preview" meta [IsParameterQuery=true, List={"v5.0-preview", "v4.0-preview", "v3.0-preview"}, DefaultValue="v4.0-preview", Type="Text", IsParameterQueryRequired=true]

Global Constants

baseURL

"https://dev.azure.com/" meta [Type="Text"]

analyticsURL

"https://analytics.dev.azure.com/" meta [Type="Text"]

Utility Function Record

utils

let
    iconSet = [
        Epic =  "👑",
        Feature = "🏆",
        #"User Story" = "📘",
        Bug = "🐞",
        #"Action Plan" = "📑",
        Document = "🖺",
        Issue = "🚩",
        Task = "📋",
        #"Test Case" = "📃",
        #"Test Plan" = "🗃",
        #"Test Suite" = "📂"
    ],

    getIcon = (wit as text) as text => Record.FieldOrDefault(iconSet, wit, "🚧"),

    formatTitle = (wit as text, title as text) as text => getIcon(wit) & " " & title,

    formatURL = (proj as text, id as number) as text => 
        baseURL & org & "/" & proj &"/_workitems/edit/"&Text.From(id),

    utils = [getIcon = getIcon, formatTitle = formatTitle, formatURL = formatURL]
in utils

Query

let 
    setup = [
        feed = "WorkItems",
        query = "$select=WorkItemId, State, Title, WorkItemType, Priority, Risk, Severity, StoryPoints, TagNames, StateCategory, ParentWorkItemId"&
    "&$expand=Area($select=AreaName,AreaPath), Project($select=ProjectId,ProjectName), Iteration($select=IterationName,IterationPath,IsEnded), AssignedTo($select=UserName)"&
        "&$orderby=WorkItemId asc",
        fullquery = feed & "?" & query
    ],
    Source = OData.Feed(analyticsURL & org & "/_odata/" & odataversion & "/" & setup[fullquery], null, [Implementation="2.0"] ),

    expand = let
        expandProject = Table.ExpandRecordColumn(
            Source,
            "Project",
            {"ProjectName", "ProjectId"}
        ),
        expandArea = Table.ExpandRecordColumn(
            expandProject,
            "Area",
            {"AreaName", "AreaPath"}
        ),
        expandIteration = Table.ExpandRecordColumn(
            expandArea
            "Iteration"
            {"IterationName", "IterationPath", "IsEnded"}
        ),
        expandAssign = Table.ExpandRecordColumn(
            expandIteration,
            "AssignedTo",
            {"UserName"},
            {"AssignedTo.UserName"}
        )
    in expandAssign,
    url = Table.AddColumn(
        expand,
        "URL",
        each utils[formatURL]([ProjectName],[WorkItemId]),
        type text
    ),
    formatTitle = Table.ReplaceValue(
        url,
        each [Title],
        each utils[formatTitle]( [WorkItemType], [Title] ),
        Replacer.ReplaceText,{"Title"})
in
    formatTitle

You will find the Power BI file with the Power Query example by folowwing this link. In this example, you will also find other cases in DAX that will be presented in the future.

Still curious?

Other blogs about optimization and advance techniques will come soon. For those who want to learn even more, follow the blog series called “Series – An agile project dashboard with Power BI” by Simon-Pierre Morin and Mathieu Boisvert.

Similar Posts