Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Can someone please help me? or is this a bug in Power Query?
Solved! Go to Solution.
I finally found the solution through the forum. Need to add Table.Buffer before the Table.Sort function to keep the sorting.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI finally found the solution through the forum. Need to add Table.Buffer before the Table.Sort function to keep the sorting.
But this table.buffer will make your pbix huge like crazy, do you have any idea how to solve it?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.