Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with pulling up latest non nullable value group by ID

The link for excel spreadsheet for this table is below :     This is how the table looks like in Power BI.My goal is to fill up the blank comments (where IsCurrentVersion=1) with last non-null valu...
  • Anonymous's avatar
    Anonymous
    4 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"