PowerQuery / M : How to debug user defined functions ?

[jct999]

Hi,

I created a function in PowerQuery/M with a few parameters and many steps.
This function should be used in other PowerQuery/M queries.

But I'm facing an error in this function and the error message is not very clear.

So my question is how can I debug the error in a self defined function ? Is there a way to check the result of each step of the function, as we can do in a standard query which is not a function ?

(Rewriting the function many times in order to return each step successively to the function's output would be an unworthy piggy method... so this is not a good answer).

Regards,

[courtin]

Hello,

I also had some nightmares while trying to debug a recursive function. The solution I found was to use the fact that PBI is good at manipulating and transforming data.

For example the function

f("x") = "x"

would become an array with actual result and debug info

f("x") = [Result="x", DebugInfo1=xxxx, DebugInfo2=xxxx,...]

In order to get the result, you call MyResult = Record.Field(f("x"), "Result")

I have put a working code sample below that you can paste into a blank query.

Regards

let

//Original function
Greetings = (Who as text, When as number) => let

Introduction = "Hello ",
Period = if When = 0 then "Morning" else "Afternoon",
Good = ", Good ",
FullGreeting = Introduction & Who & Good & Period & "!"

in

FullGreeting,

//Function with debug added
GreetingsDebug = (Who as text, When as number, optional Debug as nullable logical) => let

Introduction = "Hello ",
Period = if When = 0 then "Morning" else "Afternoon",
Good = ", Good ",
FullGreeting = Introduction & Who & Good & Period & "!",

NeedDebug = if Debug = null then false else Debug,
DebugOutput = if NeedDebug then [Period = Period, Good = Good, When = When] else [], //I can include as much steps I want
FuncOutput = Record.Combine({[Result = FullGreeting], DebugOutput})

in

FuncOutput,

//Calls to the functions

GoodMorningBobOriginal = Greetings("Bob", 0), //Calling the original function
GoodMorningBobNew = Record.Field(GreetingsDebug("Bob", 0), "Result"), //Calling the new function in production; you need to get right field in the record
GoodMorningBobNewDebug = GreetingsDebug("Bob", 0, true) //Calling the function in debug mode and retrieving all info at once

in

GoodMorningBobNewDebug

[AlB]

Hi @jct999 

Just make a normal query out of the steps of the function. Assign some value(s) to the inut argument(s). You'll be then able to see what is going on step by step

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[AllisonKennedy]

It depends on what the function is. You can use 'helper queries' to show the output of the function for each step, and then convert that query into a function. In this case, if you want the helper query to stay linked to the function, you must always update the helper query and cannot make any changes to the function directly. Whatever change you make in the helper query will carry through to the function.

Can you paste your M code here so we can be more specific? 

Otherwise, if you can create a New Query that follows the steps of your function (using 'current' values for the parameters as sample), then right click on the query name and convert to function. 

[jct999]

Hi

I can't copy my code because it should be too difficult to explain what it does, and I would like a solution that is suitable for all cases ...

What to you mean about 'helper queries' ?

One remark : Creating and testing a new query "B" with some parts of a function "A", in order to test the function "A" is a very bad solution, because :

1) You do not test function A in a dynamic context, you just test query B in a static.

2) It id a big waste of time, if you have a bunch of functions to test with a bunch of parameters.

;o(

[AllisonKennedy]

@jct999  When testing a function you will always need to test it with a given parameter. It is just the nature that with some parameters it will error while others won't. If you know what your function is doing, then you can have a better idea which parameters are causing problems and use those for your testing. 

See if these posts help for showing how to turn Queries into Functions using parameters and therefore giving you step by step error debugging: 

https://allisonkennedycv.blogspot.com/2020/04/use-parameters-to-combine-data.html

https://www.poweredsolutions.co/2019/02/19/parameters-and-functions-in-power-bi-power-query-custom-f...