Forum Discussion
Solved
Create a filtred table based on an agregation function
Hi, I have a table formatted as following : AlertID | status | Date 1 |To analyse | 01/02/2021 3 |To analyse | 03/02/2021 2 |To analyse ...
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"AlertID", Int64.Type}, {"status", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"AlertID"}, {{"Max", each List.Max([Date]), type nullable date}, {"All", each _, type table [AlertID=nullable number, status=nullable text, Date=nullable date]}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"status", "Date"}, {"status", "Date"}), #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each [Max]=[Date]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max", "Custom"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"AlertID", Order.Ascending}}) in #"Sorted Rows"Hope this helps.
Hello,
My aim is not to display the table but to clean it, keeping only the last status for each alert, that would allow me to know how many alerts has to be treated , how many have been closed etc...
Can you please help me?
thank you.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AlertID", Int64.Type}, {"status", type text}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"AlertID"}, {{"Max", each List.Max([Date]), type nullable date}, {"All", each _, type table [AlertID=nullable number, status=nullable text, Date=nullable date]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"status", "Date"}, {"status", "Date"}),
#"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each [Max]=[Date]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max", "Custom"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"AlertID", Order.Ascending}})
in
#"Sorted Rows"Hope this helps.
That's exactely what i was looking for, thank you !
You are welcome.