Active Member
November 27, 2020
Goodday,
I hope someone can help me.
My source-table has multiple columns. I need Power Query to create a new table that has all columns but:
if a value in column A occurs more then once, then remove oldest row (based on date in column C)
I've managed to get a table with only the newest values (so no duplicates) with List.Max but then I'm lost.
Here is what I have sofar (attached file has all the details)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"b_number", type text},{"customer_no", Int64.Type}, {"last_updated_at", type datetime}, {"creation_date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"b_number"}, {{"LUa", each List.Max([#"last_updated_at"]), type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"LUa", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"b_number", Order.Ascending}})
in
#"Sorted Rows"
Your help is very much appreciated.
July 16, 2010
Hi Heleen,
In the file attached I used Power Query to number grouped rows. I had to add Table.Buffer to the Sorted Rows step because the sort order doesn't stick otherwise.
I hope that helps.
Mynda
Answers Post
1 Guest(s)