cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
milomilo2020
Frequent Visitor

M code to retrieve latest Date for repeated ID's

Hi, I have a table with multiples ID's that can be in more than a ROW. They have different dates on each row

I need to replicate the following in a custom column in M , power query:

Logic:

There are repeated ID's with different dates. I need to retrieve the latest date. If the Date is the latest, then I need to have TODAY()

For example, ID = 1200

It appears in two rows. In the first one, 3/1/2022, the expected result is 8/1/2022, because there is another row with ID 1200 and latest date. On the other hand, the row with ID = 1200 and Date = 8/1/2022 has TODAY(), because there is no other newest date for that ID.

milomilo2020_0-1654088035097.png

Also the dataset has another column which is a row ID.

DATASET for test: https://drive.google.com/file/d/16K8CjMfNRv1CXpMcZZQ5HKtA_dw6G4c4/view?usp=sharing

Thanks!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @milomilo2020 

 

You can follow below steps. 

 

1. Select ID column, group by ID column and add two new columns. 

Max Date: Max on Date column;

All Data: All Rows 

vjingzhang_0-1654485813938.png

 

2. Expand All Data column and select the columns you want to keep. 

vjingzhang_1-1654485931689.png

 

3. Add a custom column with 

if [Date] = [Max Date] then Date.From(DateTime.LocalNow()) else [Max Date]

 

4. Sort by Index (Row ID) column, reorder column positions and remove Max Date column. You will get the expected result. 

 vjingzhang_2-1654486165741.png

 

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDQAhCETRXjibCAO49kLsvw2VvZhwInlkfgQJNRJmPgcMdOlCqwXhOl7XdL1u/rilW+mMdC8d8XyMEgLn4ysl/ItZFnoWawM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, ID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"ID", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Max Date", each List.Max([Date]), type nullable date}, {"All Data", each _, type table [Index=nullable number, ID=nullable number, Date=nullable date]}}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Index", "Date"}, {"Index", "Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Data", "Custom", each if [Date] = [Max Date] then Date.From(DateTime.LocalNow()) else [Max Date]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Max Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "ID", "Date", "Custom"})
in
    #"Reordered Columns"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

Hi @milomilo2020 

 

You can follow below steps. 

 

1. Select ID column, group by ID column and add two new columns. 

Max Date: Max on Date column;

All Data: All Rows 

vjingzhang_0-1654485813938.png

 

2. Expand All Data column and select the columns you want to keep. 

vjingzhang_1-1654485931689.png

 

3. Add a custom column with 

if [Date] = [Max Date] then Date.From(DateTime.LocalNow()) else [Max Date]

 

4. Sort by Index (Row ID) column, reorder column positions and remove Max Date column. You will get the expected result. 

 vjingzhang_2-1654486165741.png

 

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDQAhCETRXjibCAO49kLsvw2VvZhwInlkfgQJNRJmPgcMdOlCqwXhOl7XdL1u/rilW+mMdC8d8XyMEgLn4ysl/ItZFnoWawM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, ID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"ID", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Max Date", each List.Max([Date]), type nullable date}, {"All Data", each _, type table [Index=nullable number, ID=nullable number, Date=nullable date]}}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Index", "Date"}, {"Index", "Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Data", "Custom", each if [Date] = [Max Date] then Date.From(DateTime.LocalNow()) else [Max Date]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Max Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "ID", "Date", "Custom"})
in
    #"Reordered Columns"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors