Sorting Matrix by Columns with Measure when connecting to a Dataset

[MarkPHarris1]

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

[smpa01]

@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

[Anonymous · Accepted solution]

No workaround is possible as far as I know. The sorting has to come from source