{"id":3536,"date":"2024-03-12T07:59:25","date_gmt":"2024-03-12T11:59:25","guid":{"rendered":"https:\/\/www.kuriosit.ca\/?p=3536"},"modified":"2025-03-10T14:14:07","modified_gmt":"2025-03-10T18:14:07","slug":"power-query-optimization-variable-functions","status":"publish","type":"post","link":"https:\/\/www.kuriosit.ca\/en\/power-query-optimisation-fonctions-variables\/","title":{"rendered":"Power Query - Optimization with functions and variables"},"content":{"rendered":"<style>.wp-block-kadence-column.kb-section-dir-horizontal > .kt-inside-inner-col > .kt-info-box3536_ef9114-94 .kt-blocks-info-box-link-wrap{max-width:unset;}.kt-info-box3536_ef9114-94 .kt-blocks-info-box-link-wrap{border-top:0px solid var(--global-palette7, #eeeeee);border-right:0px solid var(--global-palette7, #eeeeee);border-bottom:0px solid var(--global-palette7, #eeeeee);border-left:0px solid var(--global-palette7, #eeeeee);border-top-left-radius:0px;background:#ffffff;padding-top:0px;padding-right:0px;padding-bottom:25px;padding-left:0px;}.kt-info-box3536_ef9114-94.wp-block-kadence-infobox{max-width:100%;}.kt-info-box3536_ef9114-94 .kadence-info-box-image-inner-intrisic-container{max-width:100px;}.kt-info-box3536_ef9114-94 .kadence-info-box-image-inner-intrisic-container .kadence-info-box-image-intrisic{padding-bottom:100%;width:297px;height:0px;max-width:100%;}.kt-info-box3536_ef9114-94 .kadence-info-box-icon-container .kt-info-svg-icon, .kt-info-box3536_ef9114-94 .kt-info-svg-icon-flip, .kt-info-box3536_ef9114-94 .kt-blocks-info-box-number{font-size:50px;}.kt-info-box3536_ef9114-94 .kt-blocks-info-box-media{border-radius:200px;overflow:hidden;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:20px;padding-right:20px;padding-bottom:20px;padding-left:20px;margin-top:0px;margin-right:10px;margin-bottom:0px;margin-left:0px;}.kt-info-box3536_ef9114-94 .kt-blocks-info-box-media .kadence-info-box-image-intrisic img{border-radius:200px;}.kt-info-box3536_ef9114-94 .kt-infobox-textcontent h2.kt-blocks-info-box-title{padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;margin-top:5px;margin-right:0px;margin-bottom:10px;margin-left:0px;}.kt-info-box3536_ef9114-94 .kt-infobox-textcontent .kt-blocks-info-box-text{color:var(--global-palette3, #1A202C);}.wp-block-kadence-infobox.kt-info-box3536_ef9114-94 .kt-blocks-info-box-text{font-size:18px;font-weight:400;}.kt-info-box3536_ef9114-94 .kt-blocks-info-box-link-wrap:hover .kt-blocks-info-box-text{color:var(--global-palette1, #3182CE);}.kt-info-box3536_ef9114-94 .kt-blocks-info-box-learnmore{background:transparent;border-width:0px 0px 0px 0px;padding-top:4px;padding-right:8px;padding-bottom:4px;padding-left:8px;margin-top:10px;margin-right:0px;margin-bottom:10px;margin-left:0px;}@media all and (max-width: 1024px){.kt-info-box3536_ef9114-94 .kt-blocks-info-box-link-wrap{border-top:0px solid var(--global-palette7, #eeeeee);border-right:0px solid var(--global-palette7, #eeeeee);border-bottom:0px solid var(--global-palette7, #eeeeee);border-left:0px solid var(--global-palette7, #eeeeee);}}@media all and (max-width: 767px){.kt-info-box3536_ef9114-94 .kt-blocks-info-box-link-wrap{border-top:0px solid var(--global-palette7, #eeeeee);border-right:0px solid var(--global-palette7, #eeeeee);border-bottom:0px solid var(--global-palette7, #eeeeee);border-left:0px solid var(--global-palette7, #eeeeee);}}<\/style>\n<div class=\"wp-block-kadence-infobox kt-info-box3536_ef9114-94\"><a class=\"kt-blocks-info-box-link-wrap info-box-link kt-blocks-info-box-media-align-left kt-info-halign-left\" target=\"_blank\" rel=\"noopener noreferrer\" href=\"https:\/\/www.linkedin.com\/in\/simon-pierre-morin-43849bb2\/\"><div class=\"kt-blocks-info-box-media-container\"><div class=\"kt-blocks-info-box-media kt-info-media-animate-none\"><div class=\"kadence-info-box-image-inner-intrisic-container\"><div class=\"kadence-info-box-image-intrisic kt-info-animate-none\"><div class=\"kadence-info-box-image-inner-intrisic\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/04\/Simon-Pierre-Morin.jpg\" alt=\"\" width=\"297\" height=\"297\" class=\"kt-info-box-image wp-image-4589\" title=\"\"><\/div><\/div><\/div><\/div><\/div><div class=\"kt-infobox-textcontent\"><p class=\"kt-blocks-info-box-text\">By Simon-Pierre Morin, Eng. <\/p><\/div><\/a><\/div>\n\n\n<style>.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-content-wrap{padding-top:var(--global-kb-spacing-xxs, 0.5rem);padding-right:var(--global-kb-spacing-xxs, 0.5rem);padding-bottom:var(--global-kb-spacing-xxs, 0.5rem);padding-left:var(--global-kb-spacing-sm, 1.5rem);border-top:1px solid var(--global-palette2, #2B6CB0);border-right:1px solid var(--global-palette2, #2B6CB0);border-bottom:1px solid var(--global-palette2, #2B6CB0);border-left:1px solid var(--global-palette2, #2B6CB0);border-top-left-radius:16px;}.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-contents-title-wrap{padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;}.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-contents-title{font-size:var(--global-kb-font-size-md, 1.25rem);font-weight:regular;font-style:normal;text-transform:uppercase;}.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-content-wrap .kb-table-of-content-list{font-size:var(--global-kb-font-size-md, 1.25rem);font-weight:regular;font-style:normal;margin-top:var(--global-kb-spacing-sm, 1.5rem);margin-right:0px;margin-bottom:0px;margin-left:0px;}.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-content-wrap .kb-table-of-content-list .kb-table-of-contents__entry:hover{color:var(--global-palette1, #3182CE);}@media all and (max-width: 1024px){.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-content-wrap{border-top:1px solid var(--global-palette2, #2B6CB0);border-right:1px solid var(--global-palette2, #2B6CB0);border-bottom:1px solid var(--global-palette2, #2B6CB0);border-left:1px solid var(--global-palette2, #2B6CB0);}}@media all and (max-width: 767px){.kb-table-of-content-nav.kb-table-of-content-id3536_3911d5-e5 .kb-table-of-content-wrap{border-top:1px solid var(--global-palette2, #2B6CB0);border-right:1px solid var(--global-palette2, #2B6CB0);border-bottom:1px solid var(--global-palette2, #2B6CB0);border-left:1px solid var(--global-palette2, #2B6CB0);}}<\/style>\n\n\n<p class=\"wp-block-paragraph\">Following on from the article on optimizing steps, I now propose to improve our working methods with Power Query so as to be more efficient and have queries that are easier to maintain. The examples mentioned in this article are based on the code obtained in the article \"<a href=\"https:\/\/www.kuriosit.ca\/en\/power-query-optimization-reduce-steps\/\" data-type=\"link\" data-id=\"https:\/\/www.kuriosit.ca\/power-query-optimisation-diminuer-les-etapes\">Reduce steps<\/a>\". Please note that the order is unimportant, and that the example file includes modifications from this entire series of articles on optimization.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">From <a href=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/3.-Power-Query-exemple-optimisation.pbix\" data-type=\"link\" data-id=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/3.-Power-Query-exemple-optimisation.pbix\">code from previous article<\/a>We improve maintainability and readability by using..:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>parameters,<\/li>\n\n\n\n<li>constants,<\/li>\n\n\n\n<li>customized functions,<\/li>\n\n\n\n<li>recordings<\/li>\n\n\n\n<li>simulation of a <em>switch-case<\/em> to replace the<em> if-else<\/em>.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"text-transform:none\">Parameters<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Parameters and constants are named global values in Power Query that can be invoked from anywhere in the query. Parameters can be easily managed in the Power Query editor, without the need for the Advanced Editor. To do so, in the \"Home\" ribbon, in the \"Parameters\" group, click on \"Manage parameters\" (Home &gt; Parameters &gt; Manage parameters).<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"455\" height=\"151\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-2.png\" alt=\"\" class=\"wp-image-3537\" title=\"\"><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"650\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-3.png\" alt=\"\" class=\"wp-image-3539\" style=\"width:453px;height:auto\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In the query editor, parameters will look like this<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"334\" height=\"109\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-4.png\" alt=\"\" class=\"wp-image-3541\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">And in the advanced editor, the parameters will look like this<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\"current value\" meta [IsParameterQuery=true, List={\"value1\", \"value2\", ...}, DefaultValue=\"default\", Type=\"...\", IsParameterQueryRequired=true]<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Where only the metadata <em>IsParameterQuery<\/em>, <em>Type<\/em> and <em>IsParameterQueryRequired <\/em>are required. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"text-transform:none\">Constants<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Constants are like parameters, except that they cannot be modified by users after publication. In their declaration, we will keep only the metadata <em>Type<\/em>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\"value\" meta [Type=\"...\"]<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In the visual editor, constants look like this<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"463\" height=\"64\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-5.png\" alt=\"\" class=\"wp-image-3543\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">For a beginner, I suggest creating the constant as a parameter to make sure of the syntax and then altering the parameter to make it a constant, but after a while you'll probably create your constants manually. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It's also possible to use global variables (queries with a single value) like this, without defining a type (thus causing the system to consider the \"Any\" type and accept all values)<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"269\" height=\"79\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-6.png\" alt=\"\" class=\"wp-image-3545\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">However, it is considered best practice to use a global constant rather than a global variable, since it forces type checking. As constants and parameters will be used in many other queries, forcing the type ensures data integrity and avoids errors. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"text-transform:none\">Customized functions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are sometimes intermediate steps that are only present to add information. We may even reuse this code more than once in all our queries. In the basic example, this is the case for the steps <em>Icon <\/em>(which prepares interim information) and <em>addTitlePlus<\/em> (which performs the formatting). Even worse than having unnecessary intermediate steps, another step is added later to remove these intermediate results. Previously, we've used a sub-step for formatting, but a greater gain in performance and maintainability could be achieved by creating a function. This function would be used to calculate the icon to be added in the addTitlePlus step.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This function can be placed in a functional query rather than in the current query, so that it can be invoked from anywhere. In the example, I've put this function in a utility function record, but this organization is optional.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>getIcon = (wit as text) as text =&gt;<br>&nbsp;&nbsp;&nbsp; if wit = \"Epic\" then \"\ud83d\udc51\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Feature\" then \"\ud83c\udfc6\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"User Story\" then \"\ud83d\udcd8\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Bug\" then \"\ud83d\udc1e\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Action Plan\" then \"\ud83d\udcd1\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Issue\" then \"\ud83d\udea9\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Task\" then \"\ud83d\udccb\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Test Case\" then \"\ud83d\udcc3\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Test Plan\" then \"\ud83d\uddc3\"<br>&nbsp;&nbsp;&nbsp; else if wit = \"Test Suite\" then \"\ud83d\udcc2\"<br>&nbsp;&nbsp;&nbsp; else \"\ud83d\udea7\"<br>,<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">And in the step for adding the formatted title column, we simply call the function<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>addTitlePlus = Table.AddColumn(<br>&nbsp;&nbsp;&nbsp; url,<br>&nbsp;&nbsp;&nbsp; \"Title+\",<br>&nbsp;&nbsp;&nbsp; each utils[getIcon]([WorkItemType]) &amp; \" \" &amp; [Title],<br>&nbsp;&nbsp;&nbsp; type text<br>)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Or, if the <em>Title <\/em>was never used without the icon, rather than adding a column, we could simply change the original column:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>formatTitle = Table.ReplaceValue(<br>&nbsp;&nbsp;&nbsp; url,<br>&nbsp;&nbsp;&nbsp; each [Title],<br>&nbsp;&nbsp;&nbsp; each utils[getIcon]([WorkItemType]) &amp; \" \" &amp; [Title],<br>&nbsp;&nbsp;&nbsp; Replacer.ReplaceText,<br>&nbsp;&nbsp;&nbsp; { \"Title\"}<br>)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Registering functions is not mandatory. It's a personal preference to have a single query named \"utils\" in which I group my utility functions rather than having one query per function. One or the other has no impact on performance. We'll see later that it can be advantageous to use records rather than leave the functions free, but at this stage, you can do as you please since there's no real gain to either. For the rest of the examples, please note that my utility functions have been put into a record.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The advantage of utility functions, in addition to saving steps and being reusable, is their maintainability. Suppose the function <em>getIcon<\/em> is used in five queries throughout the project. If, after a while, we wanted to add a new type (e.g. \"Documentation\") that wasn't already present, we'd only have to modify the utility function to add the type in question and assign it its icon. We're also no longer dependent on column names, since their contents are passed as parameters. This allows us to have the same behavior when detached from the container. We could even go a step further and create a formatting function (taking type and title as parameters) to standardize the formatting of titles. This function can be used to create or modify columns:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>formatTitle = (wit as text, title as text) as text =&gt; getIcon(wit) &amp; \" \" &amp; title,<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, I've reused the <em>getIcon<\/em> already present in my utility request, but the two functions could have been just one.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This function is called up in the same way as above:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>formatTitle = Table.ReplaceValue(<br>&nbsp;&nbsp;&nbsp; url,<br>&nbsp;&nbsp;&nbsp; each [Title],<br>&nbsp;&nbsp;&nbsp; each utils[formatTitle]( [WorkItemType], [Title] ),<br>&nbsp;&nbsp;&nbsp; Replacer.ReplaceText,<br>&nbsp;&nbsp;&nbsp; { \"Title\"}<br>)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here's what the utility request for this example might look like, with the addition of a URL formatting function that takes into account whether \"org\" is a parameter or a global constant:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>let\n\n    getIcon = (wit as text) as text =&gt;\n        if wit = \"Epic\" then \"\ud83d\udc51\"\n        else if wit = \"Feature\" then \"\ud83c\udfc6\"\n        else if wit = \"User Story\" then \"\ud83d\udcd8\"\n        else if wit = \"Bug\" then \"\ud83d\udc1e\"\n        else if wit = \"Action Plan\" then \"\ud83d\udcd1\"\n        else if wit = \"Issue\" then \"\ud83d\udea9\"\n        else if wit = \"Task\" then \"\ud83d\udccb\"\n        else if wit = \"Test Case\" then \"\ud83d\udcc3\"\n        else if wit = \"Test Plan\" then \"\ud83d\uddc3\"\n        else if wit = \"Test Suite\" then \"\ud83d\udcc2\"\n        else \"\ud83d\udea7\",\n\n    formatTitle = (wit as text, title as text) as text =&gt; getIcon(wit) &amp; \" \" &amp; title,\n\n    formatURL = (proj as text, id as number) as text =&gt;\n        \"https:\/\/dev.azure.com\/\"&amp; org &amp; \"\/\" &amp; proj &amp;\"\/_workitems\/edit\/\"&amp;Text.From(id),\n\n    utils = [\n        getIcon = getIcon,\n        formatTitle = formatTitle,\n        formatURL = formatURL\n    ]\n\nin utils<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This technique is only really useful if we need to perform these operations more than once. Nevertheless, when it comes to maintainability, performance and readability, custom functions are still advantageous.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"text-transform:none\">The power of recordings<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There is still room for improvement in the current solution. The structure <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">if...elseif...else<\/mark> brings a loss of efficiency, but many fall back on it because the structure <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">switch...case<\/mark> does not exist in Power Query. However, there is a trick to being as efficient as a <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">switch<\/mark>is to use a Record.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In our utility query, let's create a record of our different item types as a key and their icon as a value.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>iconSet = [\n   Epic = \"\ud83d\udc51\",\n   Feature = \"\ud83c\udfc6\",\n   # \"User Story\" = \"\ud83d\udcd8\",\n   Bug = \"\ud83d\udc1e\",\n   # \"Action Plan\" = \"\ud83d\udcd1\",\n   Document = \"\ud83d\uddba\",\n   Issue = \"\ud83d\udea9\",\n   Task = \"\ud83d\udccb\",\n   # \"Test Case\" = \"\ud83d\udcc3\",\n   # \"Test Plan\" = \"\ud83d\uddc3\",\n   # \"Test Suite\" = \"\ud83d\udcc2\"\n]<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Please note that keys are considered to be variable names and must meet the same nomenclature criteria. It is therefore imperative to enclose names that include special characters (spaces, accents, hyphens) in quotation marks preceded by a brace (#), as can be seen with # \"User Story\", # \"Action Plan\" and test items.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this way, it is possible to modify our function <em>getIcon <\/em>like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>getIcon = (wit as text) as text =&gt; Record.FieldOrDefault(iconSet, wit, \"\ud83d\udea7\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">where our default value is entered in the <em>FieldOrDefault<\/em> rather than in our conditional structure. Essentially, this function asks you to return the value of the key <em>wit<\/em> (WorkItem Type) of our record <em>iconSet<\/em>. This operation is almost instantaneous, since it's a reference, rather than taking a few milliseconds for each condition test, which can be extremely time-consuming for more complex checks. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The same applies to any type of operation requiring a value-based condition. As records are a key-value structure, where the key must be a string and the value is of type ANY, anything is possible (a primitive value, a function, a list, another record, or even a table). Keep in mind that records are by far the most versatile structure in Power Query, and when used properly, can simplify a complex problem.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"text-transform:none\">Conclusion - current solution<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are several ways to optimize Power Query requests. Putting everything we've seen in this article and the previous one together, we get the solution presented below. As a bonus, I've added the transformation of local configuration variables into records. There's no noticeable gain, apart from the fact that the configuration ends up in a single step containing all our variables at the start of the query.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Global parameters<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">org<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>\"KuriosIT\" meta [IsParameterQuery=true, Type=\"Text\", IsParameterQueryRequired=true]<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">odataversion<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>\"v4.0-preview\" meta [IsParameterQuery=true, List={\"v5.0-preview\", \"v4.0-preview\", \"v3.0-preview\"}, DefaultValue=\"v4.0-preview\", Type=\"Text\", IsParameterQueryRequired=true]<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Global constants<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">baseURL<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>\"https:\/\/dev.azure.com\/\" meta [Type=\"Text\"]<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">analyticsURL<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>\"https:\/\/analytics.dev.azure.com\/\" meta [Type=\"Text\"]<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Recording utility functions<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">utilities<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>let\n    iconSet = [\n        Epic = \"\ud83d\udc51\",\n        Feature = \"\ud83c\udfc6\",\n        # \"User Story\" = \"\ud83d\udcd8\",\n        Bug = \"\ud83d\udc1e\",\n        # \"Action Plan\" = \"\ud83d\udcd1\",\n        Document = \"\ud83d\uddba\",\n        Issue = \"\ud83d\udea9\",\n        Task = \"\ud83d\udccb\",\n        # \"Test Case\" = \"\ud83d\udcc3\",\n        # \"Test Plan\" = \"\ud83d\uddc3\",\n        # \"Test Suite\" = \"\ud83d\udcc2\"\n    ],\n\n    getIcon = (wit as text) as text =&gt; Record.FieldOrDefault(iconSet, wit, \"\ud83d\udea7\"),\n\n    formatTitle = (wit as text, title as text) as text =&gt; getIcon(wit) &amp; \" \" &amp; title,\n\n    formatURL = (proj as text, id as number) as text =&gt;\n        baseURL &amp; org &amp; \"\/\" &amp; proj &amp;\"\/_workitems\/edit\/\"&amp;Text.From(id),\n\n    utils = [getIcon = getIcon, formatTitle = formatTitle, formatURL = formatURL]\nin utils<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Request<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>let\n    setup = [\n        feed = \"WorkItems\",\n        query = \"$select=WorkItemId, State, Title, WorkItemType, Priority, Risk, Severity, StoryPoints, TagNames, StateCategory, ParentWorkItemId\"&amp;\n    \"&amp;$expand=Area($select=AreaName,AreaPath), Project($select=ProjectId,ProjectName), Iteration($select=IterationName,IterationPath,IsEnded), AssignedTo($select=UserName)\"&amp;\n        \"&amp;$orderby=WorkItemId asc\",\n        fullquery = feed &amp; \"?\" &amp; query\n    ],\n    Source = OData.Feed(analyticsURL &amp; org &amp; \"\/_odata\/\" &amp; odataversion &amp; \"\/\" &amp; setup[fullquery], null, [Implementation=\"2.0\"] ),\n\n    expand = let\n        expandProject = Table.ExpandRecordColumn(\n            Source,\n            \"Project\",\n            { \"ProjectName\", \"ProjectId\"}\n        ),\n        expandArea = Table.ExpandRecordColumn(\n            expandProject,\n            \"Area\",\n            { \"AreaName\", \"AreaPath\"}\n        ),\n        expandIteration = Table.ExpandRecordColumn(\n            expandArea\n            \"Iteration\"\n            {\"IterationName\", \"IterationPath\", \"IsEnded\"}\n        ),\n        expandAssign = Table.ExpandRecordColumn(\n            expandIteration,\n            \"AssignedTo\",\n            {\"UserName\"},\n            {\"AssignedTo.UserName\"}\n        )\n    in expandAssign,\n    url = Table.AddColumn(\n        expand,\n        \"URL\",\n        each utils[formatURL]([ProjectName],[WorkItemId]),\n        type text\n    ),\n    formatTitle = Table.ReplaceValue(\n        url,\n        each [Title],\n        each utils[formatTitle]( [WorkItemType], [Title] ),\n        Replacer.ReplaceText,{\"Title\"})\nin\n    formatTitle<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">You will find the Power BI file that includes this Power Query example at <a href=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/3.-Power-Query-exemple-optimisation.pbix\" data-type=\"link\" data-id=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/3.-Power-Query-exemple-optimisation.pbix\">following this link<\/a>. This example also includes some DAX cases, which will be presented in the next article.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"text-transform:none\">Still curious?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">More articles on optimization and advanced techniques are to come. For those who wish to continue and learn more, I invite them to follow the blog series entitled \"&nbsp;<a href=\"https:\/\/www.kuriosit.ca\/en\/?p=3379\">Series - Creating an agile dashboard with Power BI<\/a>&nbsp;\"by Simon-Pierre Morin and Mathieu Boisvert.<\/p>","protected":false},"excerpt":{"rendered":"<p>Still with the idea of optimizing our data acquisition, there are also methods for optimizing the way we work. Let's take a look at how you can become a more efficient editor using functions and variables.<\/p>","protected":false},"author":37,"featured_media":3578,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[43],"tags":[],"class_list":["post-3536","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intelligence-daffaires-et-ingenierie-de-donnees"],"acf":[],"taxonomy_info":{"category":[{"value":43,"label":"Intelligence d'affaires et ing\u00e9nierie de donn\u00e9es"}]},"featured_image_src_large":["https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/stepsAI_02.jpeg",1024,1024,false],"author_info":{"display_name":"Simon-Pierre Morin","author_link":"https:\/\/www.kuriosit.ca\/en\/author\/spmorin\/"},"comment_info":0,"category_info":[{"term_id":43,"name":"Intelligence d'affaires et ing\u00e9nierie de donn\u00e9es","slug":"intelligence-daffaires-et-ingenierie-de-donnees","term_group":0,"term_taxonomy_id":43,"taxonomy":"category","description":"","parent":0,"count":5,"filter":"raw","cat_ID":43,"category_count":5,"category_description":"","cat_name":"Intelligence d'affaires et ing\u00e9nierie de donn\u00e9es","category_nicename":"intelligence-daffaires-et-ingenierie-de-donnees","category_parent":0}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/posts\/3536","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/comments?post=3536"}],"version-history":[{"count":5,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/posts\/3536\/revisions"}],"predecessor-version":[{"id":4609,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/posts\/3536\/revisions\/4609"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/media\/3578"}],"wp:attachment":[{"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/media?parent=3536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/categories?post=3536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/tags?post=3536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}