Sort is not applied once column is expanded

[alexaffut]

Hi I have this Connection Query that contains a column with a list using this formula :

let
    Source = Table.NestedJoin(#"Nom Soumission", {"OV_OBJECTPART"}, #"M-Add02: ID Add+M-Add01", {"OV_OBJECTPART"}, "M02 : ID Add+M01", JoinKind.LeftOuter),
    #"Expanded M02 : ID Add+M01" = Table.ExpandTableColumn(Source, "M02 : ID Add+M01", {"Num+Date"}, {"Num+Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded M02 : ID Add+M01",{{"Num+Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"OV_OBJECTPART"}, {{"Addenda", each _, type table [OV_OBJECTTYPE=number, OV_OBJECTPART=number, Nom projet=text, #"Num+Date"=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(
    Table.Transpose(
        Table.Distinct(
            Table.FromList(
                Table.Column([Addenda],"Num+Date")
            )
        )
    ),Combiner.CombineTextByDelimiter("; #(cr)")
)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Addenda"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Addenda"}})
in
    #"Renamed Columns"

Once I merge this table with an other one the data stays the way as intended.
But once the new column is expanded, the sorting is not applied.

Sort not appliedAs intended

Can someone please help me? or is this a bug in Power Query?

[edhans]

It isn't a bug, but to the best of my knowledge, sorts do not survive a merge/expand operation. The Table.ExpandTableColumn doesn't see to have any parameter that would sort either. You'd need to apply sorts after the expansion.

[alexaffut · Accepted solution]

I finally found the solution through the forum. Need to add Table.Buffer before the Table.Sort function to keep the sorting.

[awawaw]

But this table.buffer will make your pbix huge like crazy, do you have any idea how to solve it?