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.
Hi All,
I want to create a new column in my table.
This column must calculate the highest date from the "END_DATE" column for lines of peoples that have the same "ID".
This is an example of how the table should work:
NAME | ID | END_DATE | New Column (Highest Date) |
Ana | 1 | 24/01/2023 | 24/01/2023 |
Gustav | 1 | 01/01/2023 | 24/01/2023 |
Louis | 35 | 10/02/2022 | 10/02/2022 |
Gabe | 35 | 10/01/2022 | 10/02/2022 |
Solved! Go to Solution.
Hi @Adn1 ,
How about this:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough). The idea is to join the query from after the grouping (calculating MaxDate per ID) with the original query before the grouping:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLVNJRUjAEEkYm+gaG+kYGRsZKsTrRSu6lxSWJZTBJoAyypE9+aWYxSM7YFEgaGugbGIEkjSA6E5NSUeQMoXKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, ID = _t, END_DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"ID", Int64.Type}, {"END_DATE", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MaxDate", each List.Max([END_DATE]), type nullable date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxDate"}, {"MaxDate"}) in #"Expanded Grouped Rows"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Adn1 ,
How about this:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough). The idea is to join the query from after the grouping (calculating MaxDate per ID) with the original query before the grouping:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLVNJRUjAEEkYm+gaG+kYGRsZKsTrRSu6lxSWJZTBJoAyypE9+aWYxSM7YFEgaGugbGIEkjSA6E5NSUeQMoXKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, ID = _t, END_DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"ID", Int64.Type}, {"END_DATE", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MaxDate", each List.Max([END_DATE]), type nullable date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxDate"}, {"MaxDate"}) in #"Expanded Grouped Rows"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
thanks!
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.