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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kolumam
Post Prodigy
Post Prodigy

Sorting a text column using Direct Query

Hi All,

 

I have a table in SQL which I import using Direct query in power BI. I have a text column with values as Inverter 1, Inverter 2 ..... Inverter 16.  The issue is I am not abloe to sort it like below.

 

Inverter 1

Inverter 2

.

.

.

Inverter 16

 

Instead it is automatically sorting in the below order,

 

Inverter 1

Inverter 10

Inverter 11

.

.

.

Inverter 9

 

Please look into the pic for more details.

Inverter.JPG

 

 

Kindly help.

 

Thanks

Krishnamurthy

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Kolumam,

 

Here I made a test to achieve your goal.

 

1. Connect a table of SQL server database using direct query.

2. Add a custom column using this formula in Query Editor of Power BI Desktop.

 

Custom = if [test] = "Inverter 1" then 1 else if [test] = "Inverter 2" then 2 else if [test] = "Inverter 3" then 3 else if [test] = "Inverter 4" then 4 else if [test] = "Inverter 5" then 5 else if [test] = "Inverter 6" then 6 else if [test] = "Inverter 7" then 7 else if [test] = "Inverter 8" then 8 else if [test] = "Inverter 9" then 9 else if [test] = "Inverter 10" then 10 else if [test]="Inverter 11" then 11 else if [test]="Inverter 12" then 12 else if [test]="Inverter 13" then 13 else if [test]="Inverter 14" then 14 else if [test]="Inverter 15" then 15 else 16

3. Sort the test column by Custom. Then we can get the result as you want.

 

image001 (1).png

 

Hope this will be helpful!

 

Regards,
Frank

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

View solution in original post

7 REPLIES 7
Kolumam
Post Prodigy
Post Prodigy

Hi All,

 

I am trying to sort by Inverter1, Inverter 2 etc...but it is not sorted properly. Please help to fix it as this is done using direct query.

Inverter.JPG

Hi Kolumam,

 

Since its in text format try using a 2- digit number , I.e  Inverter 01, Inverter 02 etc. while defining the switch statement.

v-frfei-msft
Community Support
Community Support

Hi @Kolumam,

 

Here I made a test to achieve your goal.

 

1. Connect a table of SQL server database using direct query.

2. Add a custom column using this formula in Query Editor of Power BI Desktop.

 

Custom = if [test] = "Inverter 1" then 1 else if [test] = "Inverter 2" then 2 else if [test] = "Inverter 3" then 3 else if [test] = "Inverter 4" then 4 else if [test] = "Inverter 5" then 5 else if [test] = "Inverter 6" then 6 else if [test] = "Inverter 7" then 7 else if [test] = "Inverter 8" then 8 else if [test] = "Inverter 9" then 9 else if [test] = "Inverter 10" then 10 else if [test]="Inverter 11" then 11 else if [test]="Inverter 12" then 12 else if [test]="Inverter 13" then 13 else if [test]="Inverter 14" then 14 else if [test]="Inverter 15" then 15 else 16

3. Sort the test column by Custom. Then we can get the result as you want.

 

image001 (1).png

 

Hope this will be helpful!

 

Regards,
Frank

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

Hi,

But I think custom columns are not allowedin Direct query. It gives an error saying - "This operation is not allowed in Direct query, switch to import mode"

 

Hello @v-frfei-msft and @Kolumam 

 

This advice doesn't work for me because I am not allowed to create a custom column in the data view. Doing so 

 

2018-10-29_15-44-41.jpg

 

 

Here are the columns I've created, trying to sort Employee Segment by Employee Segment Order ascending:

2018-10-29_15-59-04.jpg2018-10-29_15-58-40.jpg

 

Thanks for any assistance you can give.

 

Best,

Carly

 

Hi @Kolumam,

 

Does that make sense? If so , could you please mark this answer as solution?

 

Regards,
Frank

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

Hi Frank @v-frfei-msft, along these lines, can you show me how to create an indexed column using between statements then sort on that column? 

 

I have a total measure based on two fields. Once it's in a visual I need to sort by the custom text descriptors. but I can't get it to work.  Normally I would add a custom column or table with an index to sort using the in query editor but I can't do it that way with Direct Query.

 

Measures:

# Employees0 = SUM(sales_marketing_report[ORGEmployees0Number])
# Employees1 = SUM(sales_marketing_report[ORGEmployees1Num])
# Employees Total = sales_marketing_report[# Employees0] + sales_marketing_report[# Employees1]

 

I've tried this but it gives me a cicular dependence error:

Employee Bands = SWITCH (
TRUE (),
IF ('sales_marketing_report'[# Employees Total] >= 0 && 'sales_marketing_report'[# Employees Total] <= 5, TRUE, FALSE) , "0 to 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 6 && 'sales_marketing_report'[# Employees Total] <= 9, TRUE, FALSE) , "6 - 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 10 &&'sales_marketing_report'[# Employees Total] <= 25, TRUE, FALSE) , "10 - 25"
, IF ('sales_marketing_report'[# Employees Total] >= 26 && 'sales_marketing_report'[# Employees Total] <= 100, TRUE, FALSE) , "26 - 100"
, IF ('sales_marketing_report'[# Employees Total] >= 100, TRUE, FALSE) , ">100"
)


My goal is to set up visuals with counts, % and totals based on those bands/ranges:

Hi Frank @v-frfei-msft, along these lines, can you show me how to create an indexed column using between statements then sort on that column? 

 

I have a total measure based on two fields. Once it's in a visual I need to sort by the custom text descriptors. but I can't get it to work.  Normally I would add a custom column or table with an index to sort using the in query editor but I can't do it that way with Direct Query.

 

Measures:

# Employees0 = SUM(sales_marketing_report[ORGEmployees0Number])
# Employees1 = SUM(sales_marketing_report[ORGEmployees1Num])
# Employees Total = sales_marketing_report[# Employees0] + sales_marketing_report[# Employees1]

 

I've tried this but it gives me a cicular dependence error:

Employee Bands = SWITCH (
TRUE (),
IF ('sales_marketing_report'[# Employees Total] >= 0 && 'sales_marketing_report'[# Employees Total] <= 5, TRUE, FALSE) , "0 to 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 6 && 'sales_marketing_report'[# Employees Total] <= 9, TRUE, FALSE) , "6 - 9"
,IF ( 'sales_marketing_report'[# Employees Total] >= 10 &&'sales_marketing_report'[# Employees Total] <= 25, TRUE, FALSE) , "10 - 25"
, IF ('sales_marketing_report'[# Employees Total] >= 26 && 'sales_marketing_report'[# Employees Total] <= 100, TRUE, FALSE) , "26 - 100"
)


My goal is to set up visuals with counts, % and totals based on those bands/ranges like the matrices below.

 

Thanks for any help!
Carly 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.