Forum Discussion
Ordering Columns
I have a bar graph with various columns and I'd like to order them in a very specific way:
IT Service Operations
IT Applications & Infrastucture
IT Infrastructure & Service Transformation
Projects & Programmes
Could someone help me out?
In Query Editor, bring up your query with the raw data. From the menu, select "Add Column" and then "Conditional Column". In the dialog, you can give this a name like DeptSort and define the conditions. Just keep adding rules until you have all of your values covered. Here is an example of what you will end up with (this is the code that will be written for you):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRJLUgsKslNzStR0lEyMlCK1YlWikIRNAaL+aKImZiABSNRBM2MlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Departments = _t, Incidents = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Departments", type text}, {"Incidents", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "DeptSort", each if [Departments] = "ADepartment" then 4 else if [Departments] = "ZDepartment" then 2 else if [Departments] = "MDepartment" then 1 else if [Departments] = "YDepartment" then 3 else 0) in #"Added Conditional Column"After that, you can use Sort By in the Data Model.
10 Replies
can you post sample data?
So, are these values in single column or multiple columns?
If they are values in a single column then you would create a calculated column to assign a number to each one and use a Sort By column.
If they are different columns, you just order them in the Axis the way you want them.
If those two options don't work, I don't understand your issue and you need to provide more detail.
You can create a custom column like this
departmentsorting = switch(true(), [department] = "IT Service Operations","1", [department] = "IT Applications & Infrastucture","2", [department] = "IT Infrastructure & Service Transformation","3", [department] = "Projects & Programmes","4")
Update:
Now, create a summarized table with all columns including custom column, now select Department coumn and sort by departmentsorting. Now, you will see the Columns in the Chart as you needed.
