Sorting with blank cells with excel/ power query

Bassmansam

Bassmansam

Bassmansam

Soldato
Joined
22 Nov 2007
Posts
4,209
If you sort the following data in ascending order by start , planned end, actual, in excel the row with actual end date value goes to the top. In power query that value goes to the bottom.

So they treat blanks differrently. Why the inconsistency , doesn’t make sense to me?

 
Last edited:
Expand. Are you trying to double sort? Sorry triple sort?

Hmm. In curly braces add the actual date descending (not tried this) or you could assign a fake date to actual if they are blank. I have had to hack a double sort in the past to force a cache request as the results would not be in the correct order.

The reason PQ differs will be it uses its own engine and M language.
 
gpuerrilla said:
Expand. Are you trying to double sort? Sorry triple sort?

Hmm. In curly braces add the actual date descending (not tried this) or you could assign a fake date to actual if they are blank. I have had to hack a double sort in the past to force a cache request as the results would not be in the correct order.

The reason PQ differs will be it uses its own engine and M language.
Click to expand...

Yea its a triple sort in ascending in order in power query . The PQ result is the one i expect so i want to mimic that in excel.
 
gpuerrilla said:
You can filter out the blanks, replace blanks with a fixed date (so they are not blank but in the future).

If you describe what end result is I can get my head around it.
Click to expand...
So, in excel if you do the triple filter (start , planned , actual), the bottom record in the image goes to the top. In power query it stays at the bottom which is what i want to happen in excel.

Thanks for your help so far.
 
You must log in or register to reply here.
Back
Top Bottom