Power BI Grouped Rows

[docs]

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? 

Note: Not all Opportunities will have 6 notes so just need a filter that makes it so the max number of notes that can be pulled through is 6 and they are the last 6 notes entered. 

[V-yubandi-msft]

Hi @docs ,
I wanted to follow up to see if your issue has been resolved. If you still need assistance or have any questions, please let us know.

[V-yubandi-msft]

Hi @docs ,

I wanted to follow up to see if your issue has been resolved or if you still need any additional information. If you require further assistance, please let us know.

Thank you.

[V-yubandi-msft]

Hi @docs ,

Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

[V-yubandi-msft]

Hi @docs ,

Thank you for engaging with the Microsoft Fabric Community.  I tested your request to limit notes per Opportunity to the last 6 entries, and the logic is working as intended.
FYI:

Now, each Opportunity displays up to 6 of the most recent notes by CreatedDate. If there are fewer than 6 notes, it will show all available notes. The data is sorted by date (newest first), grouped by Opportunity, and filtered to show the top 6 notes for each.

I’ve included the transformation steps I followed for your reference. Please review and let me know if you’d like any changes.

regards,
Yugandhar.

[MasonMA · Accepted solution]

@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

[ivana_tomekova]

Hi... After you sorted your rows and before creating concatenated text, you can add column, which will rank the notes from 1 to n over the objectid... then remove all that are bigger than 6 and then concatenate 😉

How to ingest that nested ranking, you can check here: https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/