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 EmployeeWhile 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"I already did it like that, but I would be interested in knowing how to do a kind of switch function in M anyway.
- artemus
Switch 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
- ImkeF
Community Champion
Hi 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.
