Forum Discussion
Sorting Matrix by Columns with Measure when connecting to a Dataset
Hi There,
I'm currently making a matrix in powerbi of dates compared to a category value, which when in a matrix looks like the below:
Name a b c d
Task1 01/01/2019 01/01/2019 01/01/2019 05/01/2019
task2 etc.
the problem is I need the ordering to be more list this:
Name a d c b
Task1 01/01/2019 05/01/2019 01/01/2019 01/01/2019
Task2 etc.
My problem is that I am using a dataset which means I have no access to change the sortbycolumn on the field with a,b,c,d or add a new calculated column. Does anyone have a workaround to be able to do this? Preferably without using the new composite model functionality.
Thanks for your help,
Mark
2 Replies
MarkPHarris1 you can create a sortOrder like this in your dataset
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lEyMNQHIiMDQ0sgx9DAQClWB5uMEU4ZYxQZUyQZE5iMEboeU5wyZjhlzFFkkO2xAMnEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Date", type date}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Task", "Date"}, {{"ad", each _, type table [Task=nullable text, Date=nullable date, Value=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad], y = Table.Sort(x,{{"Value", Order.Descending}}), z = Table.AddIndexColumn(y, "Index",1,1) in z), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Task", "Date", "Value", "Index"}, {"Task", "Date", "Value", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] = 1)), #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Task"}, {{"ad", each _, type table [Task=text, Date=date, Value=number, Index=number]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let x = [ad], y = Table.AddIndexColumn(x,"sortOrder",1,1) in y), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}), #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Task", "Date", "Value", "Index", "sortOrder"}, {"Task", "Date", "Value", "Index", "sortOrder"}), Custom1 = Table.SelectRows(#"Expanded Custom", each ([Index] <> 1)), #"Merged Queries" = Table.NestedJoin(Custom1, {"Task"}, Table.Group(#"Expanded Custom1", {"Task"}, {{"maxSortOrder", each List.Max([sortOrder]), type number}}), {"Task"}, "Table (3)", JoinKind.LeftOuter), #"Expanded Table (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table (3)", {"maxSortOrder"}, {"maxSortOrder"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Table (3)",{"Index"}), #"Grouped Rows2" = Table.Group(#"Removed Columns", {"Task"}, {{"ad", each _, type table [Task=text, Date=date, Value=number, maxSortOrder=nullable number]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each let x = [ad], y=List.Max(x[maxSortOrder])+1, z = Table.AddIndexColumn(x,"sortOrder",y,1) in z), #"Removed Other Columns2" = Table.SelectColumns(#"Added Custom2",{"Custom"}), #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Custom", {"Task", "Date", "Value", "maxSortOrder", "sortOrder"}, {"Task", "Date", "Value", "maxSortOrder", "sortOrder"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Index"})&Table.RemoveColumns(#"Expanded Custom2",{"maxSortOrder"}), #"Grouped Rows3" = Table.Group(#"Removed Columns1", {"Task"}, {{"ad", each _, type table [Task=text, Date=date, Value=number, sortOrder=number]}}), #"Removed Other Columns3" = Table.SelectColumns(#"Grouped Rows3",{"ad"}), #"Expanded ad" = Table.ExpandTableColumn(#"Removed Other Columns3", "ad", {"Task", "Date", "Value", "sortOrder"}, {"Task", "Date", "Value", "sortOrder"}) in #"Expanded ad"and enforce the sortOrder in the matrix as following to come to the desired
