Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Col 1 | Col 2 | Col 3 | Desired Result |
4 | + | 5 | 9 |
3 | - | 2 | 1 |
Let's say I have above dataset:
To do above, I could write a formula in Power QUery something like "if [col 2] = "+" then [Col 1] + [Col 3] else [Col 1] - [Col 3]. I DO NOT want this sort of solution.
Instead, I would like the value of [Col 2] to be inherent within the formula. Perhaps with some 'mythical' formula like below:
[Col 1] MadeUpFunction[Col 2] [Col 3]
Is there any sort of way to affect the Power Query code using a field in your dataset?
Solved! Go to Solution.
Yes, there is a way.
Use Expression.Evaluate
You can build a string from the first 3 columns ( either by appending with & or use Text.Combine and then just pass that to Expression.Evaluate()
Thank you, so much. This is exactly what I am looking for. I am curious about one thing though.
Expression.Evaluate(Text.Combine({"5","=","5"}, " ")) will return true. Which is good.
Expression.Evaluate(Text.Combine({"hello","=","hello"}, " ")) will return an error. "The name 'hello' doesn't exist in the current context."
Do you know why we cannot use "=" operator for alphabetical comparisons?
There's nothing wrong with the "="
See the 3rd example
https://learn.microsoft.com/en-us/powerquery-m/expression-constant#example-3
You need Expression.Constant
Thanks for the reply, but could you be a bit more specific how Expression.Constant is used? Is this wrapped inside of the Expression.Evaluate?
The documenation is extremely short on this function and I do not know what it does exactly.
Yes, there is a way.
Use Expression.Evaluate
You can build a string from the first 3 columns ( either by appending with & or use Text.Combine and then just pass that to Expression.Evaluate()
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
101 | |
97 | |
66 | |
59 |