Forum Discussion
Help with pulling up latest non nullable value group by ID
- Anonymous4 years ago
Found the solution:
let
Source = Excel.Workbook(File.Contents("C:\Users\smoeller\Downloads\Excel Source.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"ID", Int64.Type}, {"Last_Modified", type datetime}, {"IsCurrentVersion", Int64.Type}, {"Comments", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last_Modified", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.FillUp(_, {"Comments"}), type table [Title=nullable text, ID=nullable number, Last_Modified=nullable datetime, IsCurrentVersion=nullable number, Comments=nullable text]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Title", "ID", "Last_Modified", "IsCurrentVersion", "Comments"}, {"Count.Title", "Count.ID", "Count.Last_Modified", "Count.IsCurrentVersion", "Count.Comments"})
in
#"Expanded Count"
Found the solution:
let
Source = Excel.Workbook(File.Contents("C:\Users\smoeller\Downloads\Excel Source.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"ID", Int64.Type}, {"Last_Modified", type datetime}, {"IsCurrentVersion", Int64.Type}, {"Comments", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last_Modified", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.FillUp(_, {"Comments"}), type table [Title=nullable text, ID=nullable number, Last_Modified=nullable datetime, IsCurrentVersion=nullable number, Comments=nullable text]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Title", "ID", "Last_Modified", "IsCurrentVersion", "Comments"}, {"Count.Title", "Count.ID", "Count.Last_Modified", "Count.IsCurrentVersion", "Count.Comments"})
in
#"Expanded Count"