cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alonam Frequent Visitor
Frequent Visitor

how to not break bars order with removing "order" field

I have a bar chart displaying value over the period (3 months buckets from youngest to oldest)

age.PNG

 

data looks like that:

age data.PNG

 

Sorting the chart didn't help, so I added column age-order so I can display it the way I need (to display values from "less than 3 months" to "older than 60 months"), I was only able to use "age-order" in the tooltip but of cause it shows in the tooltip

 

so the question is: are there other options? I don't want the end user to see it in the tooltip

 

Thank you

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: how to not break bars order with removing "order" field

Hi @Alonam ,

 

In your scenario, we should insert an age column in power query and make the table sorted by the custom column.We can split the Age column firstly, then we can insert a custom column by the splited columns and remove the unnecessary columns. Here is the M code for your reference.

l

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc87CsAgEEXRvUxt4S9G1yIiQgQLoxDdP4EQmGHqA493Y4Re18q7lZFNvufYbYEABUlQckiaUUAyjJRGs9w82sFMWzTHzJArJzNLNj0vkGjhs9mv+hD8yyWk9AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Age = _t, values = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}, {"values", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Age", "Age - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Age", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Age.1", "Age.2", "Age.3", "Age.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Age.1", type text}, {"Age.2", type text}, {"Age.3", Int64.Type}, {"Age.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Age.2", "Age.4"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Age.1]="less" then [Age.3] else if [Age.1] = "older" then [Age.3]+1 else [Age.3]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Age.1", "Age.3"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Custom", Order.Ascending}})
in
#"Sorted Rows"

 

Then we can get the result as we excepted.

 

Capture.PNGresult.PNG

PBIX as attached.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: how to not break bars order with removing "order" field

Hi @Alonam ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
acbg Member
Member

Re: how to not break bars order with removing "order" field

@Alonam within Data select Age column and go to Modeling tab (at the top) and go to Sort by Column and sort by age_order.

 

This should sort your Age column in the graph now automatically without needing to do sort by.

Alonam Frequent Visitor
Frequent Visitor

Re: how to not break bars order with removing "order" field

@acbg sorry I meant age-order column (updated desc)

when I follow that the "Sort by column" use Age by default if I switch to age_order (the one that I need it  to be sorted by) I receive an error

 

sort by age.PNG

 

end removing age-order from the tooltip breaks the order

order is broken.PNG

Community Support Team
Community Support Team

Re: how to not break bars order with removing "order" field

Hi @Alonam ,

 

In your scenario, we should insert an age column in power query and make the table sorted by the custom column.We can split the Age column firstly, then we can insert a custom column by the splited columns and remove the unnecessary columns. Here is the M code for your reference.

l

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc87CsAgEEXRvUxt4S9G1yIiQgQLoxDdP4EQmGHqA493Y4Re18q7lZFNvufYbYEABUlQckiaUUAyjJRGs9w82sFMWzTHzJArJzNLNj0vkGjhs9mv+hD8yyWk9AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Age = _t, values = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}, {"values", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Age", "Age - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Age", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Age.1", "Age.2", "Age.3", "Age.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Age.1", type text}, {"Age.2", type text}, {"Age.3", Int64.Type}, {"Age.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Age.2", "Age.4"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Age.1]="less" then [Age.3] else if [Age.1] = "older" then [Age.3]+1 else [Age.3]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Age.1", "Age.3"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Custom", Order.Ascending}})
in
#"Sorted Rows"

 

Then we can get the result as we excepted.

 

Capture.PNGresult.PNG

PBIX as attached.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: how to not break bars order with removing "order" field

Hi @Alonam ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.