Forum Discussion
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
- edhansCommunity 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.
- alexaffutNew Member
I finally found the solution through the forum. Need to add Table.Buffer before the Table.Sort function to keep the sorting.
- AnonymousNot applicable
But this table.buffer will make your pbix huge like crazy, do you have any idea how to solve it?