Forum Discussion

alexaffut's avatar
alexaffut
New Member
6 years ago
Solved

Sort is not applied once column is expanded

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?

 

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

3 Replies

  • edhans's avatar
    edhans
    Community Champion

    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's avatar
      alexaffut
      New Member

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

      • Anonymous's avatar
        Anonymous
        Not applicable

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