Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
That's a somewhat confusing subject line.
I'll try and keep this short as I suspect there's a very easy solution for someone with more Power Query knowledge than me.
I'm adding a column to a table using a custom function.
In some cases (depending on the parameters passed) there is no result returned by this function.
This produces the new column (as intended) but all values are null (not the type that can be replaced).
I would like to know the most efficient/elegant way to handle this scenario and return 0 instead of null.
Thanks,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Solved! Go to Solution.
Hi @KNP,
you have 2 options: you integrate a test for null into you function or you do that after calling it. The later looks like:
= Table.AddColumn( LastStep, "NewColumnName", each let functionResult = myFunction(_, another param, another param), in if functionResult = null then 0 else functionResult )
The important thing is that you call your custom function only once and save the result into a variable.
A proof: The first query runs for 5 seconds, the other for 10 seconds.
/* let Source = Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)), Result = if Source = null then 0 else Source in Result */ let Result = if Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)) = null then 0 else Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)) in Result
Hi @KNP,
you have 2 options: you integrate a test for null into you function or you do that after calling it. The later looks like:
= Table.AddColumn( LastStep, "NewColumnName", each let functionResult = myFunction(_, another param, another param), in if functionResult = null then 0 else functionResult )
The important thing is that you call your custom function only once and save the result into a variable.
A proof: The first query runs for 5 seconds, the other for 10 seconds.
/* let Source = Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)), Result = if Source = null then 0 else Source in Result */ let Result = if Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)) = null then 0 else Function.InvokeAfter(() => 5, #duration(0, 0, 0, 5)) in Result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |