Forum Discussion

docs's avatar
docs
Frequent Visitor
10 months ago
Solved

Power BI Grouped Rows

I have created a grouped row which shows all notes related to an object id but I only want the last 6 notes created to be shown in this grouped row. Is there any filters I can put in place to do this...
Need Help
Show and Tell
Tips and Tricks
  • MasonMA's avatar
    MasonMA
    10 months ago

    docs 

     

    Hi, ideally you would do this before grouping the rows. but if you wanted to add after your last step, you can also use below logic. It's tested working on my end. 

     

    let
    SplitNotes = Table.ExpandListColumn(
        Table.TransformColumns(Grouped Rows, {"Combined Notes", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)}),
        "Combined Notes"
    ),

    AddDate = Table.AddColumn(SplitNotes, "NoteDate", each Date.FromText(Text.Start([Combined Notes], 10)), type date),

    Sorted = Table.Sort(AddDate, {{"objectid", Order.Ascending}, {"NoteDate", Order.Descending}}),

    Grouped = Table.Group(Sorted, {"objectid"}, {{"Top6Notes", each Table.FirstN(_, 6), type table [objectid=nullable number, Combined Notes=nullable text, NoteDate=nullable date]}}),

    CombineNotes = Table.TransformColumns(
        Grouped,
        {"Top6Notes", each Text.Combine([Combined Notes], "#(lf)"), type text}
    )
in
    CombineNotes

     

MasonMA's avatar
MasonMA
Super User
10 months ago

docs 

 

Hi, ideally you would do this before grouping the rows. but if you wanted to add after your last step, you can also use below logic. It's tested working on my end. 

 

let
    SplitNotes = Table.ExpandListColumn(
        Table.TransformColumns(Grouped Rows, {"Combined Notes", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)}),
        "Combined Notes"
    ),

    AddDate = Table.AddColumn(SplitNotes, "NoteDate", each Date.FromText(Text.Start([Combined Notes], 10)), type date),

    Sorted = Table.Sort(AddDate, {{"objectid", Order.Ascending}, {"NoteDate", Order.Descending}}),

    Grouped = Table.Group(Sorted, {"objectid"}, {{"Top6Notes", each Table.FirstN(_, 6), type table [objectid=nullable number, Combined Notes=nullable text, NoteDate=nullable date]}}),

    CombineNotes = Table.TransformColumns(
        Grouped,
        {"Top6Notes", each Text.Combine([Combined Notes], "#(lf)"), type text}
    )
in
    CombineNotes