How to sort the Year Column in Matrix

[kapil512]

Hi,

I have the data like below, and i want to sort the order 2017 to 2010 on Year Column Matrix.

			2012	2013	2014	2015
Column A	Column B	Column C	YTD
a	b	c	1000	2000	3000	4000
d	e	f	5000	7000	8000	9000

I want to start from 2015 to 2012.

Thanks,

Kapil

[v-caliao-msft · Accepted solution]

@kapil512,

As Phil_Seamark said, you need to add a order column in query editor. I have tested it on my local environment, here is a sample PBIX file for you reference.

Sample data.

Group	Year	Amount
a	2012	72
a	2013	118
a	2014	83
a	2015	76
b	2012	96
b	2013	58
b	2014	87
b	2015	80
c	2012	120
c	2013	88
c	2014	62
c	2015	93

Add a index column inside each group, and the sample query looks like below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc4xDsAgCIXhuzA7CFbFsxiHtve/Q3kMFaeXfAl/mJNuSiSZxaYLrfRLsWHWSJeNligVZ83l2aFxCEJVo3inR0FHs8u7OywHIaQaBaEmURAa9uL6AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Year = _t, Amount = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Year", Int64.Type}, {"Amount", Int64.Type}}),
    Partition = Table.Group(ChangedType, {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Year", Order.Descending}}), "Index",1,1), type table}}),
    #"Expanded Partition"= Table.ExpandTableColumn(Partition, "Partition", {"Year", "Amount", "Index"}, {"Year", "Amount", "Index"})
in
    #"Expanded Partition"

Results.

Then you could cort your matrix column using this index column.

Regards,

Charlie Liao

[kapil512]

Thank you So much Phil and Charlie.

Its worked.

Thanks,

kapil

[Phil_Seamark]

HI @kapil512

Create another column in your table that will be used just for sorting.  You may need to do this in the Query Editor if possible, and make the values negative, So 2012 becomes -2012 (negative 2012) and 2013 becomes -2013 etc etc

Then use the "Sort by column" to say your Year column should use your new column to control sorting.