Sort text by ascending and make nulls last

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)

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

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"

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.