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 have data similar to the following:
ID | Date1 | Date2 | Date3 |
1 | 2017/07/01 09:00:00 | 2020/09/10 12:00:00 | 2019/03/12 11:00:00 |
2 | 2017/07/01 10:00:00 | null | null |
3 | 2017/07/01 09:30:00 | null | 2020/05/03 09:00:00 |
4 | 2017/07/01 09:45:00 | 2020/08/03 09:35:00 | null |
I would like to create a new column [Last updated] containing the latest date/time value out of each of the 3 columns on a given row
ID | Date1 | Date2 | Date3 | Last updated |
1 | 2017/07/01 09:00:00 | 2020/09/10 12:00:00 | 2019/03/12 11:00:00 | 2020/09/10 12:00:00 |
2 | 2017/07/01 10:00:00 | null | null | 2017/07/01 10:00:00 |
3 | 2017/07/01 09:30:00 | null | 2020/05/03 09:00:00 | 2020/05/03 09:00:00 |
4 | 2017/07/01 09:45:00 | 2020/08/03 09:35:00 | null | 2020/08/03 09:35:00 |
I have tried using conditional columns but it seems to lack the fidelity to do something like this.
Solved! Go to Solution.
Hi @lightw0rks ,
you can add a custom column with the following formula:
It allows you to add additional Date columns as well. It skips the first column of the table.
But it could easily be changed to a solution where the date-columns are hardcoded if the dynamic solution is not desired.
This is the code to try out:
let
Source = #table(
{"ID", "Date1", "Date2", "Date3"},
List.Zip(
{
{"1", "2", "3", "4"},
{"01/07/2017 09:00", "01/07/2017 10:00", "01/07/2017 09:30", "01/07/2017 09:45"},
{"10/09/2020 12:00", "null", "null", "03/08/2020 09:35"},
{"12/03/2019 11:00", "null", "03/05/2020 09:00", "null"}
}
)
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Date1", type datetime}, {"Date2", type datetime}, {"Date3", type datetime}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each List.Max(List.Skip(Record.FieldValues(_)))
)
in
#"Added Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@lightw0rks Best to unpivot probably but if not, MC Aggregations: https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...
Hi @lightw0rks ,
you can add a custom column with the following formula:
It allows you to add additional Date columns as well. It skips the first column of the table.
But it could easily be changed to a solution where the date-columns are hardcoded if the dynamic solution is not desired.
This is the code to try out:
let
Source = #table(
{"ID", "Date1", "Date2", "Date3"},
List.Zip(
{
{"1", "2", "3", "4"},
{"01/07/2017 09:00", "01/07/2017 10:00", "01/07/2017 09:30", "01/07/2017 09:45"},
{"10/09/2020 12:00", "null", "null", "03/08/2020 09:35"},
{"12/03/2019 11:00", "null", "03/05/2020 09:00", "null"}
}
)
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Date1", type datetime}, {"Date2", type datetime}, {"Date3", type datetime}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each List.Max(List.Skip(Record.FieldValues(_)))
)
in
#"Added Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks, that's great!
I needed a harcoded solution as I had a few more columns going on in my data so I modified it slightly:
List.Max(Record.FieldValues(Record.SelectFields(_, "Date1", "Date2", "Date3")))
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.