Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
if your problem is solved then please accept this as a 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)
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.
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"
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.
if your problem is solved then please accept this as a 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)
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |