Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi, I want to be able to manually sort these Matrix rows in a specific sort that I need it in; for example, I would like "REVENUE" to always be first, so for example:
REVENUE
LABOR
TEMP LABOR
SUBCONTRACTORS
ETC....
How can I go about getting this data to show in this sort orde
Solved! Go to Solution.
Hi @Dali748
Sorry, i have not found any simple way of just adding a column.
sorting in matrix may be on ascending or descending, but columns created in edit queries can't change with that selection.
I find a more simple way is add two column in edit queries, the step is simple, just use UI,
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You have to create a number column and key in the sort order they way you want like below.
REVENUE | 1 |
LABOR | 2 |
TEMP LABOR | 3 |
SUBCONTRACTORS | 4 |
The select the Category column and choose SORT BY and select your sort column.
Refer document.
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
If it helps, mark it as a solution
Kudos are nice too
Thank you for the replies!
@VasTg Where do I create this number column? Do I have to to it in the data source?
For example, I have thousands of rows for "REVENUE", "TEMP LABOR", etc. that roll up to this matrix; do I just have to add a new column in the data source with this number sort break down? Or can I do this in the "PowerBI>Desktop>Edit Query" screen?
Thank you!
Hi @Dali748
Yes, you can add a column in Edit queries.
First sort [Category] column on Ascending, then add an index column from 1,
then add a conditional column(called "ASC") : if [CATEGORY]="REVENUE" then 0 else [Index],
then add another custom column(called "DEC"): if [CATEGORY]="REVENUE" then List.Max(#"Added Conditional Column"[Index])+1 else [Index]
let
Source = Excel.Workbook(File.Contents("C:24\2.24.xlsx"), null, true),
Sheet4_Sheet = Source{[Item="Sheet4",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet4_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CATEGORY", type text}, {"DATE", type date}, {"VALUE", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"CATEGORY", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Added Conditional Column" = Table.AddColumn(#"Added Index", "ASC", each if [CATEGORY] = "REVENUE" then 0 else [Index]),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "DEC", each if [CATEGORY] = "REVENUE" then List.Max(#"Added Conditional Column"[Index])+1 else [Index]),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ASC", Order.Ascending}})
in
#"Sorted Rows1"
If you select sort by ascending of [CATEGORY] column, you could select the [CATEGORY] column, then select Sort by Column->select the column [ASC],
If you select sort by descending of [CATEGORY] column, you could select the [CATEGORY] column, then select Sort by Column->select the column [DEC],
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-juanli-msft Thank you for the reply!
So I would have to add 3 additional columns (Index, ASC, DES) within Edit queries?
Would this be easier if I just added 1 "Category_Sort" column in my raw Excel data using a formula to show the sort order I want the categories in?
Thank you!
Hi @Dali748
Sorry, i have not found any simple way of just adding a column.
sorting in matrix may be on ascending or descending, but columns created in edit queries can't change with that selection.
I find a more simple way is add two column in edit queries, the step is simple, just use UI,
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I prefer to do this in powerbi with measure or calculated column method as powerquery causes performance slowdown. Also, column headings cannot be sorted with what is explained here!
Example column names:
"A B C D"
Names of desired columns:
"D B A C"
I think you didn't understand the main point.
@v-juanli-msft Ok, thank you!
And this method will also work with multiple rows of the same category; right? For example:
USAGE
REVENUE
REVENUE
LABOR
REVENUE
USAGE
USAGE
LABOR
ETC...
Hi @Dali748
As tested, it works in this case.
Matrix would group data based on the "row" field, so we may find the "REVENUE" or "USAGE" only once under "row" list.
Best Regards
Maggie
@v-juanli-msft Ok, yes that would work; as long as it groups it by row in the matrix even if there are multiple rows in the raw data for the same category. Ty.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
94 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
146 | |
112 | |
73 | |
56 |