When working with cloud data sources you might encounter links where spaces show up as “%20” instead for example. This is called URI escaping or encoding. In Power Query there is a native function that does this kind of encoding: Uri.EscapeDataString – PowerQuery M | Microsoft Learn However, a native function that does just the opposite is missing. Fortunately, this can be achieved with a custom function that I have found in the Power BI forum: Uri.UnescapeDataString This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To re ..read more

For Azure SQL Databases there is a very cool new preview feature: “sp_invoke_external_rest_endpoint “. This function allows you do call certain Microsoft API endpoints directly from within your Azure database and write that data back into a table for example. With that, you can for example create a stored procedure that can be triggered from Power Automate. This is ideal for larger datasets that would require long and slow “apply-to-each” rounds or cumbersome bulk-upload-workarounds. I was struggling with the authentication when using a system assigned managed identity (“service principal”). T ..read more

This article presents a method to automatically plug or balance your balance sheet in Power BI. You can use it if your bookkeeping system doesn’t contain the Profit and Loss (PnL) bookings yet. Or for planning or financial modelling purposes when your calculated balance sheet figures don’t add up. The problem to plug or balance your balance sheet If you are not familiar with the problem: In the double bookkeeping world, entries made to the system will always add up to zero. For example, if you are selling an item for 100 Euros and receiving these 100 Euros in cash for it, this transaction will ..read more

When you reference another query in Power Query you will automatically get the results of the final step. But what if you want to reference a step that sits within that referenced query? One solution would be to split up that query into 2 and reference the final result of the splitted query. But that could result in additional query time, as the data might be pulled from the source twice (once for each query). A potentially better alternative is to add the result of the intermediate step as metadata of the query itself. That way you could pick steps just as you need them. To do so, you add a ..read more

While Excel and DAX have native functions to calculate the payment amount for a loan, Power Query lacks it still. As this is a very useful function, it’s time to create our own Payment function (PMT) for Power Query: Excel’s payment function (PMT) for Power Query Excel’s PMT function returns the payment for a loan based on constant payments and a constant interest rate. Using some clever math, it can be deducted via one formula (see rows 9-11): This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in ..read more

Finally 2023 is here, the year we expect Power Query function libraries to ship. This will make it so much easier to re-use ready made function than M-extensions. So let’s start collecting some fodder for it, by creating a function to calculate the Present Value (PV) for Power Query: Excel’s Present Value (PV) function for Power Query Excel’s PV function returns the present value of a loan or an investment, based on a constant interest rate. Fortunately, this function is relatively new in the DAX-language so that I can simply borrow the calculation logic from Greg Deckler, who created tons of ..read more

Formula.Firewall issues can hit you when designing your queries or even “out of the blue” when suddenly refreshes in the service are failing due to changes in the query evaluation. You will find a lot of methods published on the internet which are good and cover different scenarios. But there is also a very quick fix method that I learned from Miguel Escobar that I want to demonstrate in this post. This will basically circumvent the data privacy level, so make sure that you understand the implications (risk of data leakage from one source to another). If not, please read Miguels article first ..read more

If you want to traverse organisational hierarchies, walk through nested folder structures or create nested Power BI metrics from an Excel table for example, you need some sort of recursive logic. In this article I will introduce you to the basic method of recursive flows in Power Automate. In upcoming articles I will share some of the flows for the above mentioned use cases. What is recursion? Daniil Maslyuk contributed a great introduction into recursion on my blog here. Please check it out if the concept of recursion is new to you. Here, I will take his basic example as a starting point to c ..read more

While Power Query provides a convenient method to fill values from selected columns up- and downwards, there is no native option to do this to the right or left. So this article provides a custom function to do so. Table.FillRight in Power Query This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters let func = (SourceTable as table, FillColumns, optional FillLeft) =&g ..read more

Have you ever come across the case where you open your dataflow and find 2 additional steps at the end of your query? Why are Power Platform Dataflows adding steps automatically at the end? Dataflows adding steps at the end of the query automatically It will be a step that replaces errors on certain columns followed by a step that transforms these columns into text. Reason This will happen for every untyped column of the table that’s going to be loaded. So unlike Power BI datasets, who accept untyped columns, dataflows don’t. What happens As text is the format that most other simple types ca ..read more