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, I have a case where I have three records with the same registration ID but with different STATUS and I need to choose the record with the most recent date. Can it be done in Power Query? How could I do it?
YEAR | MY | IDREGISTRO | ESTADO |
2022 | 9 | RT2325963 | Assigned |
2022 | 10 | RT2325963 | Earring |
2022 | 11 | RT2325963 | Earring |
2023 | 2 | RT2325963 | Closed |
2022 | 10 | RT9899967 | Assigned |
2023 | 2 | RT9899967 | Closed |
Thank you.
Solved! Go to Solution.
Hi @LoloGox ,
I created a sample pbix file(see the attachment), please check if that is what you want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUbIE4qAQI2MjU0szYyDbsbg4Mz0vNUUpVgeuxtAATZFrYlFRZl46ihpD/GpAQkZoSpxz8oux2mRpYWlpaWaOxTkIYxBqYMbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YEAR = _t, MY = _t, IDREGISTRO = _t, ESTADO = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"MY", Int64.Type}, {"IDREGISTRO", type text}, {"ESTADO", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"IDREGISTRO"}, {{"Most recent date", each List.Max([YEAR]), type nullable number}, {"Details", each _, type table [YEAR=nullable number, MY=nullable number, IDREGISTRO=nullable text, ESTADO=nullable text]}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"YEAR", "MY", "ESTADO"}, {"YEAR", "MY", "ESTADO"}),
#"Added Custom" = Table.AddColumn(#"Expanded Details", "Flag", each if [Most recent date]=[YEAR] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Most recent date", "Flag"})
in
#"Removed Columns"
Best Regards
Hi @LoloGox ,
I created a sample pbix file(see the attachment), please check if that is what you want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUbIE4qAQI2MjU0szYyDbsbg4Mz0vNUUpVgeuxtAATZFrYlFRZl46ihpD/GpAQkZoSpxz8oux2mRpYWlpaWaOxTkIYxBqYMbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YEAR = _t, MY = _t, IDREGISTRO = _t, ESTADO = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"MY", Int64.Type}, {"IDREGISTRO", type text}, {"ESTADO", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"IDREGISTRO"}, {{"Most recent date", each List.Max([YEAR]), type nullable number}, {"Details", each _, type table [YEAR=nullable number, MY=nullable number, IDREGISTRO=nullable text, ESTADO=nullable text]}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"YEAR", "MY", "ESTADO"}, {"YEAR", "MY", "ESTADO"}),
#"Added Custom" = Table.AddColumn(#"Expanded Details", "Flag", each if [Most recent date]=[YEAR] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Most recent date", "Flag"})
in
#"Removed Columns"
Best Regards
@Syndicate_Admin , refer code in the blog can help
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |