![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/a-screenshot-of-a-computer-ai-generated-content-m.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Figure 1 \u2013 Default Find & Replace that doesn\u2019t account for case sensitivity<\/p>\n\n\n\n
The low-code UI doesn\u2019t give you the option on case insensitivity, so it creates this M code:<\/p>\n\n\n\n
Table.ReplaceValue(#\"Added Custom\",\"hello\",\"Good Morning\",Replacer.ReplaceText,{\"Column A\"})<\/pre><\/div>\n\n\n\nSo, to make it case insensitive you need to change the M code to this:<\/p>\n\n\n\n
#\"Replaced Value\" = Table.ReplaceValue(\n #\"Added Custom\",\n each [Column A],\n each Text.Combine(\n List.ReplaceMatchingItems(\n Text.Split([Column A], \" \"),\n {{\"hello\", \"Good Morning\"}},\n Comparer.OrdinalIgnoreCase\n ),\n \" \"\n ),\n Replacer.ReplaceText,\n {\"Column A\"}\n)<\/pre><\/div>\n\n\n\nThis increases the complexity of the M code, but will properly replace words \u201chello\u201d, \u201cHello\u201d, or \u201cHeLLo\u201d and replace it with the word \u201cGood Morning\u201d.<\/p>\n\n\n\n
Keep case sensitivity in mind whenever using text replaces or if you\u2019re using other functions like Text.Contains<\/code> or Text.StartsWith<\/code>.<\/p>\n\n\n\nCredit to Rick De Groot<\/a> for teaching me this one.<\/p>\n\n\n\n2. Sort and Remove Duplicates<\/h2>\n\n\n\n
With timestamped data, I\u2019ve often seen users sort by timestamp and then remove duplicates based on an ID column. However, due to how Power Query streams data during refresh, it won\u2019t retain that sort order unless you explicitly preserve it.<\/p>\n\n\n\n
Use Table.Buffer<\/code> to wrap your Table.Sort<\/code> step and ensure the sorted table is held in memory before applying Table.Distinct<\/code>.<\/p>\n\n\n\nBefore:<\/p>\n\n\n\n
#\"Sorted Rows\" = Table.Sort(#\"Changed Type\",{{\"Timestamp\", Order.Ascending}}),\n\n#\"Removed Duplicates\" = Table.Distinct(#\"Sorted Rows\", {\"ID\"})<\/pre><\/div>\n\n\n\nAfter:<\/p>\n\n\n\n
#\"Sorted Rows\" = Table.Sort(#\"Changed Type\",{{\"Timestamp\", Order.Ascending}}),\n TableBuffer = Table.Buffer(#\"Sorted Rows\",BufferMode.Delayed),\n#\"Removed Duplicates\" = Table.Distinct(#\"Sorted Rows\", {\"ID\"})<\/pre><\/div>\n\n\n\n3. No Regular Expressions Support<\/h2>\n\n\n\n
Unlike many languages, Power Query (M) doesn’t support regular expressions. If you’re used to extracting or cleaning multiline text using regex, you’ll need to use alternative options (outside of Power Query). Fabric Notebooks have this capability with pyspark.sql.functions.regexp<\/code>, so that is alternative is you have access to Microsoft Fabric.<\/p>\n\n\n\n4. Merges are Expensive<\/h2>\n\n\n\n
Merge steps can significantly slow down development, especially with large datasets, because they require scanning entire tables. Ideally, place merges later in your query. The less processing you do after them, the faster your development will be.<\/p>\n\n\n\n
In addition, you can apply these best practices to lower the cost of merge operations:<\/p>\n\n\n
\n\n- Reduce columns before merging to increase performance. <\/li>\n\n\n\n
- Use
Table.AddKey<\/code> to create an index to improve merge performance. <\/li>\n\n\n\n- If needed, use a parameter like
Is_Development = true<\/code> to limit rows during development. Set it to false before publishing. <\/li>\n<\/ul>\n<\/div>\n\n\nCredit to Chris Webb<\/a> for his great content on this topic.<\/p>\n\n\n\n5. Rounding Numbers Isn\u2019t What You Expect<\/h2>\n\n\n\n
Number.Round<\/code> doesn\u2019t follow traditional rounding rules. By default, it uses Bankers Rounding<\/a>. This means that when two numbers are the same difference from two integers, it will favor the nearest even integer.<\/p>\n\n\n\nSo in Figure 2, you\u2019d expect 24.5 to round to 25 if you\u2019re rounding to the nearest whole number by using Number.Round([Value],0)<\/code>.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/a-screenshot-of-a-computer-ai-generated-content-m-1.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Figure 2 \u2013 Number.Round with Bankers Round<\/p>\n\n\n\n
If you wish numbers to round in the \u201cnormal way\u201d, meaning they always round up when they end in .5, you would need to update the Number.Round<\/code> function to be:<\/p>\n\n\n\nNumber.Round([Value], 0, RoundingMode.AwayFromZero)<\/pre><\/div>\n\n\n\nAs result, the rounding to the nearest whole number works as expected.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/a-screenshot-of-a-computer-ai-generated-content-m-2.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Figure 3 \u2013 Rounding fixed<\/p>\n\n\n\n
Credit to Celia Alves<\/a> for teaching me this aspect of Power Query.<\/p>\n\n\n\n6. Parameters Should Not Be ‘Any’ Type<\/h2>\n\n\n\n
Parameters are a powerful way to make your models more flexible, especially when moving from development to production environments. But here\u2019s the issue: if your parameter is of type Any<\/code>, it cannot be updated in the Power BI Service. There is nothing more frustrating that finishing your work, publishing to the Power BI Service, going to update a parameter and have the parameter disabled. It\u2019s never convenient and you have to update the model and publish the changes again.<\/p>\n\n\n\nAlways set parameters like URLs or server names to type Text (see Figure 4) if you plan to change them after publishing.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-4.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Figure 4 \u2013 Example of setting a parameter as Text<\/p>\n\n\n\n
As a result, you can set value in the Power BI service versus getting a disabled input field (see Figure 5)<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/a-screenshot-of-a-computer-ai-generated-content-m-3.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Figure 5 \u2013 Remember to set your parameters as Text instead of the default value of Any.<\/p>\n\n\n\n
7. Enable Load vs. Include in Refresh<\/h2>\n\n\n\n
Right-click on a table in Power Query and the options shown in Figure 6 can be quite confusing:<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-6.png\")
<\/figure>\n\n\n\n
Figure 6 \u2013 Confusing Options?<\/p>\n\n\n\n
Both options can be checked or unchecked independently. Here\u2019s what each one does:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-7.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-8.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/a-yellow-background-with-black-text-ai-generated.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-10.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-11.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-12.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/06\/word-image-107030-13.png\")
<\/figure>\n\n\n\n