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.
I'm hoping to find a way to make a column with the most recent of three dates in a row for three other columns with nulls
Lets say I have a table with dogs and dates they learned three tricks. Note that some dogs only learn one, two or zero tricks instead of all three tricks.
I'm hoping to add a column that shows the date of the most recent trick the dog learned (example result below):
I have explored a couple of potential issues that don't quite work for my situation, but I'm including them here in case they work for someone else.:
Pivoting and Group By:
1) Un-pivot the three trick columns (Sit, Speak, Roll Over), resulting in the below table:
M code: #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Previous Step", {"Dog Name"}, "Attribute", "Value"),
2) Group By Dog name to find the most recent date associated with each dog.
M code for this: #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Dog Name"}, {{"Most Recent Trick", each List.Max([Value]), type datetime}})
The problem: Buster (who never learned any tricks and has nulls in all three date columns) disappears from the table. I would like to keep Buster on the table with a null or "No tricks learned" in the Most Recent Trick column.
If Else
I'm finding that Power Query gives me errors if I compare nulls.
One option would be to replace the nulls with a different value (such as #date(1901, 1, 1) so they could be compared but I'm nervous about altering the data in this manner.
This is my M code:
#"Added Conditional Column" =
Table.AddColumn(#"Previous Step",
"Most Recent Trick",
each if
[Sit] >= [Speak]
and [Sit] >= [#"Roll Over"]
then [Sit]
else if
[Speak] >= [#"Roll Over"]
and [Speak] >= [Sit]
then [Speak]
else if
[#"Roll Over"] >= [Speak]
and [#"Roll Over"] >= [Sit]
then [#"Roll Over"]
else null)
I have also tried using Number.From() to compare the trick dates as numbers instead of dates:
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns",
"Most Recent Trick",
each if
Number.From([Sit]) >= Number.From([Speak])
and Number.From([Sit]) >= Number.From([#"Roll Over"])
then [Sit]
else if
Number.From([Speak]) >= Number.From([#"Roll Over"])
and Number.From([Speak]) >= Number.From([Sit])
then [Speak]
else if
Number.From([#"Roll Over"]) >= Number.From([Speak])
and Number.From([#"Roll Over"]) >= Number.From([Sit])
then [#"Roll Over"]
else null)
Does anyone have a different solution in mind that could work for Power Query? My ideal solution would look something like this:
- Would result in a new column that gives me the most recent date of the three tricks the dog learned for each row
- Keep all of the dogs on the table (including the dog with null values in the other three columns)
- Would not give me errors for comparing nulls
- Would not require me to alter the data (ie by replacing the nulls with a 1/1/1901 or other fake date)
This is a table you can copy and paste for dummy data if you would like:
Dog Name | Sit | Speak | Roll Over |
Bruno | 1/2/2015 | 2/14/2015 | 4/5/2015 |
Jim | 5/4/2021 | ||
Harold | 3/1/2015 | 3/1/2015 | |
Ollie | 8/1/2015 | ||
Minnie | 9/29/2015 | ||
Lulu | 12/13/2015 | 12/1/2021 | |
Buster | |||
Fido | 7/6/2015 |
Solved! Go to Solution.
Try a custom column
List.Max({[Sit],[Speak], [Roll Over]})
This worked perfectly! I used the UI to create a custome column and copied and pasted your code into the box.
Before I had tried a solution with List.Max({...}) but I was typing it directly into Advanced Editor and I think I didn't get the code right. I am still very new to PQ and M code so it was probably an error on my part before.
If anyone else in a similar situation as me (new to M, needs a solution like mine, tried to code with List.Max in the Advanced Editor before and got errors), I encourage you to use the Custom Column button on the UI and just copy and paste the List.Max code in there.
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.