{"id":3521,"date":"2024-02-27T10:31:30","date_gmt":"2024-02-27T15:31:30","guid":{"rendered":"https:\/\/www.kuriosit.ca\/?p=3521"},"modified":"2025-03-10T14:15:14","modified_gmt":"2025-03-10T18:15:14","slug":"power-query-optimization-reduce-steps","status":"publish","type":"post","link":"https:\/\/www.kuriosit.ca\/en\/power-query-optimisation-diminuer-les-etapes\/","title":{"rendered":"Power Query - Optimize with fewer steps"},"content":{"rendered":"<style>.wp-block-kadence-column.kb-section-dir-horizontal > .kt-inside-inner-col > .kt-info-box3521_4ac8ed-2f .kt-blocks-info-box-link-wrap{max-width:unset;}.kt-info-box3521_4ac8ed-2f .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-box3521_4ac8ed-2f.wp-block-kadence-infobox{max-width:100%;}.kt-info-box3521_4ac8ed-2f .kadence-info-box-image-inner-intrisic-container{max-width:100px;}.kt-info-box3521_4ac8ed-2f .kadence-info-box-image-inner-intrisic-container .kadence-info-box-image-intrisic{padding-bottom:100%;width:297px;height:0px;max-width:100%;}.kt-info-box3521_4ac8ed-2f .kadence-info-box-icon-container .kt-info-svg-icon, .kt-info-box3521_4ac8ed-2f .kt-info-svg-icon-flip, .kt-info-box3521_4ac8ed-2f .kt-blocks-info-box-number{font-size:50px;}.kt-info-box3521_4ac8ed-2f .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-box3521_4ac8ed-2f .kt-blocks-info-box-media .kadence-info-box-image-intrisic img{border-radius:200px;}.kt-info-box3521_4ac8ed-2f .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-box3521_4ac8ed-2f .kt-infobox-textcontent .kt-blocks-info-box-text{color:var(--global-palette3, #1A202C);}.wp-block-kadence-infobox.kt-info-box3521_4ac8ed-2f .kt-blocks-info-box-text{font-size:18px;font-weight:400;}.kt-info-box3521_4ac8ed-2f .kt-blocks-info-box-link-wrap:hover .kt-blocks-info-box-text{color:var(--global-palette1, #3182CE);}.kt-info-box3521_4ac8ed-2f .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-box3521_4ac8ed-2f .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-box3521_4ac8ed-2f .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-box3521_4ac8ed-2f\"><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-id3521_aa6a82-bd .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-id3521_aa6a82-bd .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-id3521_aa6a82-bd .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-id3521_aa6a82-bd .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-id3521_aa6a82-bd .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-id3521_aa6a82-bd .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-id3521_aa6a82-bd .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\">In the wonderful world of data acquisition with Power Query, it can happen that some queries take longer than others, even in the preview, which only loads the first 1000 lines. There are several ways to optimize queries in Power Query. This article presents some of them, with their advantages and disadvantages.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The reason why this article is considered intermediate is that, by using these techniques, you will lose the ability to use the visual editor to modify your optimized steps. It is still possible to use the visual editor to create new steps and then modify them using the advanced editor.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Base created with visual editor<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">For the rest of this article, I'm going to use this basic example, which would be done in the Power Query editor without using the advanced editor. For the sake of readability, I've taken the liberty of spacing out the code in the advanced editor, since normally each step is on a single line.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>let\n    Source = OData.Feed(\n        \"https:\/\/analytics.dev.azure.com\/HypershellTechno\/zzz_Formation\/_odata\/v4.0-preview\/WorkItems?$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        null,\n        [Implementation=\"2.0\"]\n    ),\n\n    expandProject = Table.ExpandRecordColumn(\n        Source,\n        \"Project\",\n        { \"ProjectName\", \"ProjectId\"},\n        {\"ProjectName\", \"ProjectId\"}\n    ),\n\n    expandArea = Table.ExpandRecordColumn(\n        expandProject,\n        \"Area\",\n        { \"AreaName\", \"AreaPath\"},\n        {\"AreaName\", \"AreaPath\"}\n    ),\n\n    expandIteration = Table.ExpandRecordColumn(\n        expandArea,\n        \"Iteration\",\n        {\"IterationName\", \"IterationPath\"},\n        {\"IterationName\", \"IterationPath\"}\n    ),\n\n    expandAssign = Table.ExpandRecordColumn(\n        expandIteration,\n        \"AssignedTo\",\n        {\"UserName\"},\n        {\"AssignedTo.UserName\"}\n        ),\n\n    Sort_byID = Table.Sort(\n        expandAssign,\n        {{\"WorkItemId\", Order.Ascending}}\n    ),\n\n    url = Table.AddColumn(\n        Sort_byID,\n        \"URL\",\n        each baseURL&amp;\"\/\"&amp;[ProjectName]&amp;\"\/_workitems\/edit\/\"&amp;Text.From([WorkItemId])\n    ),\n\n    icon = Table.AddColumn(url, \"Icon\",\n        each\n            if [WorkItemType] = \"Epic\" then \"\ud83d\udc51\"\n            else if [WorkItemType] = \"Feature\" then \"\ud83c\udfc6\"\n            else if [WorkItemType] = \"User Story\" then \"\ud83d\udcd8\"\n            else if [WorkItemType] = \"Bug\" then \"\ud83d\udc1e\"\n            else if [WorkItemType] = \"Action Plan\" then \"\ud83d\udcd1\"\n            else if [WorkItemType] = \"Issue\" then \"\ud83d\udea9\"\n            else if [WorkItemType] = \"Task\" then \"\ud83d\udccb\"\n            else if [WorkItemType] = \"Test Case\" then \"\ud83d\udcc3\"\n            else if [WorkItemType] = \"Test Plan\" then \"\ud83d\uddc3\"\n            else if [WorkItemType] = \"Test Suite\" then \"\ud83d\udcc2\"\n            else \"\ud83d\udea7\"\n     ),\n\n    addTitlePlus = Table.AddColumn(\n        icon,\n        \"Title+\",\n        each [Icon] &amp; \" \" &amp; [Title]\n    ),\n\n    del_icon = Table.RemoveColumns(addTitlePlus,{\"Icon\"})\n\nin del_icon<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The query steps look like this in the editor:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"232\" height=\"222\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-8.png\" alt=\"\" class=\"wp-image-3554\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">It's important to remember that Power Query stores the previews of each of the intermediate steps, and must recalculate them every time there's a change or we update the data. Many of these steps have no added value in being stored in memory. We can therefore optimize both execution time and memory space used.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Merging nested steps<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">One possibility is to nest the steps within each other. We could therefore think of modifying the four column expansions as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>expand = Table.ExpandRecordColumn(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table.ExpandRecordColumn(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table.ExpandRecordColumn(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table.ExpandRecordColumn(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Source<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Project<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { \"ProjectName\", \"ProjectId\"}<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Area<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { \"AreaName\", \"AreaPath\"}<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Iteration<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { \"IterationName\", \"IterationPath\"}<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AssignedTo<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { \"UserName\"}<br>&nbsp;&nbsp;&nbsp; ),<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In the editor and for Power Query, this is treated as a single step called \"expand\", rather than the four steps previously described.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"359\" height=\"247\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-1.png\" alt=\"\" class=\"wp-image-3534\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This is the most optimal method in terms of performance. The downside is that, in addition to losing the gear for editing in the interface, it becomes more difficult to do the maintenance caused by poor readability (here, I've taken the trouble to indent the code with line feeds. However, when it's on a single line, it becomes virtually impossible to know which parameter goes with which function).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This example shows an extreme case. The typical use case for nesting is usually limited to two steps, not four as shown here.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Sub-steps - the operator <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">let...in<\/mark><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Another way of reducing the number of intermediate steps in the editor without losing legibility is to use <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">let...in<\/mark>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;&nbsp;&nbsp; expand = let\n        expandProject = Table.ExpandRecordColumn(\n            Source,\n            \"Project\",\n            { \"ProjectName\", \"ProjectId\"}\n        ),\n\n        expandArea = Table.ExpandRecordColumn(\n            expandProject,\n            \"Area\",\n            { \"AreaName\", \"AreaPath\"}\n        ),\n\n        expandIteration = Table.ExpandRecordColumn(\n            expandArea,\n            \"Iteration\",\n            { \"IterationName\", \"IterationPath\", \"IsEnded\"}\n        ),\n\n        expandAssign = Table.ExpandRecordColumn(\n            expandIteration,\n            \"AssignedTo\",\n            {\"UserName\"},\n            {\"AssignedTo.UserName\"}\n        )\n\n    in expandAssign,<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This technique reduces the number of intermediate steps by treating them as a single one. A single preview is also calculated. In terms of performance, this technique is comparable to nesting. There will still be a difference from nesting due to the creation of local variables, but the impact on memory is negligible.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"359\" height=\"247\" src=\"https:\/\/www.kuriosit.ca\/wp-content\/uploads\/2024\/02\/image-1.png\" alt=\"\" class=\"wp-image-3534\" title=\"\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In addition to the similar benefits of nesting, we get a bonus in terms of readability and maintainability. It's actually very easy to debug these steps, since by undoing the <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">let...in<\/mark>the steps will reappear in the editor. Note, however, that the step (and therefore the reference for subsequent steps) becomes the name of the variable preceding the <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">let <\/mark>and not the variable name visible after the <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">in<\/mark>. To avoid forgetting during debugging, you could always call your last step by the same name as your variable, but this is optional. (And don't forget to reset the comma if it's not the last step).<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Eliminate redundant or unnecessary steps<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A title like this seems obvious, but did you notice that a sorting step had been added even though the OData query already does this sorting? The same applies to type modifications. In the basic example, the step had not been added, but to comply with best practice, the type of the new URL column should have been text rather than any. It's also possible that you used ReplaceValue rather than ReplaceText when modifying your values. Typically, we would add the following step to adjust for erroneous types:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>changeType = Table.TransformColumnTypes(formatTitle,{{\"Title\", type text}, {\"URL\", type text}})<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note that if you have made all your type changes at the same time, the editor will already make a list. On the other hand, it sometimes performs these steps at different times<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><mark class=\"has-inline-color has-kb-palette-1-color\">changeType = Table.TransformColumnTypes(formatTitle,{ {\"Title\", type text} })\n<\/mark>{...}<mark class=\"has-inline-color has-kb-palette-1-color\">\nchangeType2 = Table.TransformColumnTypes(otherOperation,{ {\"URL\", type text} })<\/mark><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This second version should be avoided, both in terms of performance and memory. It's much more efficient to do it all at once. However, there's an even simpler way to avoid this step altogether.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For some reason, the graphics editor doesn't allow you to add this parameter, but adding it manually doesn't prevent you from using the graphics editor. When the column is created, you specify the column type directly. So here's the step as written by the editor.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>url = Table.AddColumn(\n    expand,\n    \"URL\",\n    each baseURL&amp;\"\/\"&amp;[ProjectName]&amp;\"\/_workitems\/edit\/\"&amp;Text.From([WorkItemId])\n),<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">And here's the version modified to indicate the type at creation.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>url = Table.AddColumn(\n    expand,\n    \"URL\",\n    each baseURL&amp;\"\/\"&amp;[ProjectName]&amp;\"\/_workitems\/edit\/\"&amp;Text.From([WorkItemId])<mark class=\"has-inline-color has-theme-palette-1-color\"><strong>,\n    type text<\/strong><\/mark>\n),<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This can also be done for manually created data. (There are several methods, but here's one that uses Record).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Table.FromRecords(\n    {\n        [ ID = 7665, StartDate = #date(2013,05,12), EndDate = null, GivenName = \"John\", FamilyName = \"Smith\" , isAnnual = true, efficiency = 2.54 ],\n        [ ID = 7788, StartDate = #date(2015,02,28), EndDate = null, GivenName = \"Jane\", FamilyName = \"Doe\", isAnnual = true, efficiency = 1.54 ],\n        [ ID = 7890, StartDate = #date(2020,03,27), EndDate = #date(2021,06,30), GivenName = \"Juliette\", FamilyName = \"Capulet\", isAnnual = false, efficiency = 0.68 ],\n        [ ID = 8756, StartDate = #date(2020,11,17), EndDate = #date(2021,06,30), GivenName = \"Romeo\", FamilyName = \"Montaigu\", isAnnual = false, efficiency = 0.35 ],\n        [ ID = 8857, StartDate = #date(2021,06,30), EndDate = null, GivenName = \"Barbara\", FamilyName = \"Delacruz\", isAnnual = false, efficiency = 3.4 ],\n        [ ID = 9563, StartDate = #date(2023,12,01), EndDate = null, GivenName = \"George\", FamilyName = \"Washington\", isAnnual = true, efficiency = 1.48 ]\n    }\n    let\n        _t = ((type nullable text) meta [Serialized.Text = true]),\n        _d = ((type nullable datetime) ),\n        _i = ((type nullable Int64.Type) ),\n        _n = ((type nullable number) ),\n        _b = ((type logical) )\n    in type table [ID = _i, StartDate = _d, EndDate = _d, GivenName = _t, FamilyName = _t, isAnnual = _b, efficiency = _n]\n)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">By adding the type to the creation, you can eliminate the conversion step altogether, which is a double gain in performance. Take the trouble to read the official documentation for the functions you're using to learn more about the various parameters that the visual editor ignores or uses incorrectly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Again to eliminate unnecessary steps, rather than adding a column for the icon, then calculating the title and then destroying the icon, you can create the change directly without any additional steps.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>formatTitle = Table.ReplaceValue(\n        url,\n        each [Title],\n        each (\n            if [WorkItemType] = \"Epic\" then \"\ud83d\udc51\"\n            else if [WorkItemType] = \"Feature\" then \"\ud83c\udfc6\"\n            else if [WorkItemType] = \"User Story\" then \"\ud83d\udcd8\"\n            else if [WorkItemType] = \"Bug\" then \"\ud83d\udc1e\"\n            else if [WorkItemType] = \"Action Plan\" then \"\ud83d\udcd1\"\n            else if [WorkItemType] = \"Issue\" then \"\ud83d\udea9\"\n            else if [WorkItemType] = \"Task\" then \"\ud83d\udccb\"\n            else if [WorkItemType] = \"Test Case\" then \"\ud83d\udcc3\"\n            else if [WorkItemType] = \"Test Plan\" then \"\ud83d\uddc3\"\n            else if [WorkItemType] = \"Test Suite\" then \"\ud83d\udcc2\"\n            else \"\ud83d\udea7\"\n        ) &amp; [Title],\n        Replacer.ReplaceText,{\"Title\"})<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">By directly concatenating our structure <mark style=\"background-color:#EDF2F7\" class=\"has-inline-color\">if...elseif...else<\/mark> and the title, we avoid two steps that were totally unnecessary, namely the creation and deletion of a column for an intermediate state. In a future article, I'll show you how to further improve this structure without using a conditional structure, but it's still a significant first improvement. Always try to see if your intermediate steps are really necessary, or if you can do more with less, as is the case here.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">Conclusion - the current solution<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are many ways to optimize Power Query requests. Putting everything we've seen in this article together, we get the solution presented below. Note that indented formatting is optional, but helps readability and has no impact on the editor or performance. I therefore suggest that, when using the advanced editor, you use a similar technique to aid readability. As a bonus, I've added a local variable that makes it easier to edit the query in the editor. I'll talk more about these variables in another article.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-medium-font-size\">Request<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>let\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\n    Source = OData.Feed(\"https:\/\/analytics.dev.azure.com\/KuriosIT\/_odata\/v4.0-preview\/WorkItems?\" &amp; query, 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 baseURL&amp;\"\/\"&amp;[ProjectName]&amp;\"\/_workitems\/edit\/\"&amp;Text.From([WorkItemId]),\n        type text\n    ),\n    formatTitle = Table.ReplaceValue(\n        url,\n        each [Title],\n        each (\n            if [WorkItemType] = \"Epic\" then \"\ud83d\udc51\"\n            else if [WorkItemType] = \"Feature\" then \"\ud83c\udfc6\"\n            else if [WorkItemType] = \"User Story\" then \"\ud83d\udcd8\"\n            else if [WorkItemType] = \"Bug\" then \"\ud83d\udc1e\"\n            else if [WorkItemType] = \"Action Plan\" then \"\ud83d\udcd1\"\n            else if [WorkItemType] = \"Issue\" then \"\ud83d\udea9\"\n            else if [WorkItemType] = \"Task\" then \"\ud83d\udccb\"\n            else if [WorkItemType] = \"Test Case\" then \"\ud83d\udcc3\"\n            else if [WorkItemType] = \"Test Plan\" then \"\ud83d\uddc3\"\n            else if [WorkItemType] = \"Test Suite\" then \"\ud83d\udcc2\"\n            else \"\ud83d\udea7\"\n        ) &amp; [Title],\n        Replacer.ReplaceText,{\"Title\"})\nin\n    formatTitle<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">You can 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 presents a few cases in DAX and another solution including functions, which will be discussed in subsequent articles.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-medium-font-size\">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>In the wonderful world of data acquisition with Power Query, it can happen that certain queries take longer to execute. There are several ways to optimize queries in Power Query. Let's take a look at some of them.<\/p>","protected":false},"author":37,"featured_media":3576,"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-3521","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_01.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\/3521","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=3521"}],"version-history":[{"count":5,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/posts\/3521\/revisions"}],"predecessor-version":[{"id":4612,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/posts\/3521\/revisions\/4612"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/media\/3576"}],"wp:attachment":[{"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/media?parent=3521"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/categories?post=3521"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kuriosit.ca\/en\/wp-json\/wp\/v2\/tags?post=3521"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}