Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BJE
Helper I
Helper I

Sorting a visual using text-based ranges

I have a table that I has a column recording the FTE for each company in the list. I have created an additional column that has created FTE ranges, i.e.:

0-4

5-9

10-24

25-49

50-99

100-149

150-199

200+

 

When I use the FTE Ranges as the axis on my visual, I can't sort in this order. If I try and sort by FTE Range it orders them as follows:

0-4

100-149

10-24

150-199

200+

25-49

50-99

5-9

 

Any ideas?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

or better could use " " (2 blanks or 1 blank depending the number of digits) instead "0"

 

image.png

View solution in original post

6 REPLIES 6
ziying35
Impactful Individual
Impactful Individual

@BJE 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WcgtxVbJSMtA1UarVgfEMDQx0DU0sUUR0jVBUmAJVWCKrMDIw0EbmmuqiGABUj6LcVBfIiwUA",BinaryEncoding.Base64),Compression.Deflate))),
    sort = Table.Sort(Source, {each Number.From(Text.SplitAny([FTE],"-+"){0}), Order.Ascending})
in
    sort

If my code solves your problem, mark it as a solution

Greg_Deckler
Super User
Super User

@BJE - Use a separate colum with numeric sort values. Use the Sort By feature to sort your text column by your numeric column.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks. Have tried that - not changing my visual.

 

BJE_0-1595481289338.png

 

Anonymous
Not applicable

if it is not acceptable for aesthetic reasons, try somethonk like this

 

image.png

Anonymous
Not applicable

or better could use " " (2 blanks or 1 blank depending the number of digits) instead "0"

 

image.png

Thanks - this worked for me

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors