Sort text by ascending and make nulls last

[PbiCeo]

Hello everyone,

When I sort text by ascending, nulls come first like this:

null

null

A

B

C

a

b

c

How can I make nulls last when I sort it by ascedning?

I could not find this fumction as GUI.

Although it is better if there is any way to do that with GUI, PQ is also okay to make this possible. 

Thanks,

Vladi

[Ajinkya369 · Accepted solution]

Hi @PbiCeo  ,You can do this by adding a conditional column.

I'm attaching the solved screenshots for your problem.

Step1: Add condtional column and enter the detail as shown in the screenshot.

Step2:Sort the Newly added condtional column in ascending order and its done.

Step3:Now Sort the column in ascending where the alphabets are present.

Step1

Step2

Step3

if your problem is solved then please accept this as a solution.

[Rickmaurinus]

You can also do something like this: 

= List.Sort(
      {"A","C","a","b","B",null,"c",null, "b", null },
      ( x,y) => Value.Compare( x??"~", 
                               y??"~"
                 )
)

 This treats the null values as if it's a "~" value. From all values, this values sort at the bottom. 

For more on list functions you can read: 

https://gorilla.bi/power-query/list-functions/

Enjoy!

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Anonymous]

if your goal is to sort a list of strings, you could get the result without using auxiliary lists

let
    Source = {"A","C","a","b","B",null,"c",null},
    #"Sorted Items" = List.Sort(Source,(x,y)=>if x&y<> null then Value.Compare(x ,y) else Value.Compare(y ,x))
in
    #"Sorted Items"

[Ajinkya369 · Accepted solution]

Hi @PbiCeo  ,You can do this by adding a conditional column.

I'm attaching the solved screenshots for your problem.

Step1: Add condtional column and enter the detail as shown in the screenshot.

Step2:Sort the Newly added condtional column in ascending order and its done.

Step3:Now Sort the column in ascending where the alphabets are present.

Step1

Step2

Step3

if your problem is solved then please accept this as a solution.

[PbiCeo]

Hello @Ajinkya369 , 

By the way, does it work for DirectQuery, too?

If not, is there any way for DQ?

[Ajinkya369 · Accepted solution]

Hi @PbiCeo ,

It wont work if you are using the direct query method.

Yes there is another way to acheive this,please follow the video link below:

https://www.youtube.com/watch?v=n_gnuFS8qoY&feature=youtu.be

Thank you

Ajinkya Kadam(Analytical specialist)

[Fowmy]

@PbiCeo 

You can add a conditional column like below and sort then delete it

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Mariusz]

Hi @PbiCeo 

You can add a conditional column to rank nulls as 2 and the rest as 1 and later sort this column first and the original column later.

= Table.AddColumn(#"Replaced Value", "Custom", each if [Column1] = null then 2 else 1)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn