Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
phull543
Frequent Visitor

Sort grouped row by created on date in Order Ascending

SortNotebyNewest.PNGI have created a column which shows all notes for each activity and the date each note was created. This has been grouped with another field from a different table. I am trying to sort this column so that the newest created note is always shown for each activity, can someone explain how to do that? It is currently showing the last created note first as can be seen in the screenshot included.

13 REPLIES 13
PwerQueryKees
Super User
Super User

The Table.Group() does not preserve sort order or defines a sort order. So if you want your groups to be sorted, you do it AFTER the sort, but on each group individually. 

 

Do the following just after your group by (check the step and field names first!):

= Table.TransformColumns(#"Grouped Rows",{{"Notes", each Table.Sort(_, {{"Latest Date", Order.Descending}})}})

 

I have added that command and I am now getting the following error.

 

error1.PNG

Can you share all the M Code. I want to see how you create the "Combined Notes" column. It is probaly after that step where you need to do the sort.

Correction. I now see that you get 2 different Notes fields, each sorted the way you want. My apologies, I reacted to quick.

Unless you created those yourself, I would stick with the solution @AntrikshSharma has provided.
If you did create those yourself, you can consider to keep the Notes as indidual rows in the table created by the group by. Then do a Table.Combine() on the 2 tables (on each row) en sort that table.

So, something like = Table.Sort(Table.Combine({Note_Table1, Notes_Table2), {{"Latest Date", Order.Descending}} in a Table.AddColumn()

Ok,  I have included it below. Let me know if you need anything else.

 

CombinedNotes.PNG

AntrikshSharma
Super User
Super User

@phull543 Use Lines.FromText

 

AntrikshSharma_0-1732284921562.png

 

And then Sort by extracting the date part

 

Ascending

AntrikshSharma_2-1732285096045.png

 

Descending

AntrikshSharma_3-1732285121078.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLRNTDVNTRT8EgsKKhUcMosKslISayMyQNLGRrpGhsqRKYmFim45qVABQ0MgUjBL7UcLAETNNc1tFDwTSwqykxMT1VwzMvLLEstKk4sqlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Events = _t]),
    AddedCustom = 
        Table.AddColumn (
            Source, 
            "Custom", 
            each List.Sort (
                Lines.FromText ( [Events] ), 
                {
                    each Date.FromText ( Text.Start ( _, 10 ), [ Format = "yyyy-MM-dd" ] ), 
                    Order.Descending
                }
            )
        )
in
    AddedCustom

 

 

 

 

@phull543 This solution doesn't work for you?

mussaenda
Super User
Super User

Hi @phull543 ,

 

Before Grouping, sort the date then group. Will that work for you?

Yes it does but once I have grouped those rows I merge with another query and this is when the issue arises. Merged queries.PNG

phull543
Frequent Visitor

I have tried to do that but it take the first date which is the oldest date and i am trying to sort by newest. 

@phull543 

 

Sort from newest to oldest then?  Sort Descending.

 

regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


I have tried to do that but it just sorts the latest date created. I need it to change the order of all the dates in the note from newest to oldest. SortOrderLatest.PNG

PhilipTreacy
Super User
Super User

@phull543 

 

Split the column into 2 columns : Date and Note, and sort by the Date. 

 

You can sort by other column(s) first to maintain some other order you might need before sorting by this new Date column.

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors