Forum Discussion
Switching from DAX to M for nested IFs and TODAY condition
DateTime.LocalNow and DateTime.FixedLocalNow
https://docs.microsoft.com/en-us/powerquery-m/datetime-localnow
ImkeF probably knows about the switch statement.
While you can kind of do switch in M, for what you have if/else is good enough.
E.g.
= if [status] = "Open" and [Due Date] < DateTime.FixedLocalNow() then "Not Paid" else if [status] = "Open" and [Due Date] >= DateTime.FixedLocalNow() then "To be Paid" else "Paid"
Microsoft EmployeeSwitch in M:
let
cases =
[
MyTerm1 = "My term one",
MyTerm2 = "My term two",
MyTerm3 = if [someOtherColumn] > 0 then "Yes" else "No"
]
in
try Record.Field(cases, [MyColumnValue]) otherwise "My default case"There is also a similar one for table syntax. Also note that perf is bad if default case is hit a lot
Community ChampionHi artemus
would very much like to understand why performance only drops if the default-value is triggered.
I was doing some performance test with record lookups and found them very fast, as they evaluated lazily. But used them without the try ... otherwise - extension.
Just curious to understand why using try...otherwise wouldn't impact performance per se, but the occurrance of the "otherwise" will cause the drop.
Or did I understand this wrong?
Thanks heaps.
- artemus
Try .. otherwise uses exception handling nativly (at least it appears this way). Since an exception can handle several levels deep, going back up the stack isn't performant.
Thought about this a bit more, this perf issue can be avoided by using Record.FieldOrDefault function.
- ImkeF
Thanks artemus for the alternative solution.
Not sure how to interpret the "levels" in your explanation: We're dealing with a record here that (at least for my tests) evaluates lazily: Just evaluates the field that matches the condition/lookup. Where are there any levels? Doesn't it return just the one field from the record?
- artemus
try .. otherwise supports multiple levels.
E.g.
try let factorialSkip = (n) => if n > 0 then n * @factorialSkip(n - 2) else if n = 1 then error "Odd numbers not allowed" else 1 in factorialSkip(9)In this case, factorialSkip is called with 9, then 7, then 5, then 3, then 1, which results in an error.