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 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
Solved! Go to Solution.
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
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
Thank you So much Phil and Charlie.
Its worked.
Thanks,
kapil
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.
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.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |