Sorting with blank cells with excel/ power query

[Bassmansam]

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?

 

[Bassmansam]

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.

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.

 

[Bassmansam]

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.

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.

 

[Bassmansam]

OK so your not using Power Query just the old school sheet and formulas?

Correct. Im just trying to replicate what i did in pq in excel

 

[Bassmansam]

OK let me think just finished work buddy, need to be away from data for a moment.

No rush man

 

[Bassmansam]

How about a helper column?

To the side of the actual date, select first one i.e. cell D2

=D2=""

Fill series down.
Blanks should be displayed as TRUE.
You should be able to third sort on this helper column to be at top/bottom.

Do you mean out that d2="" in cell e2?

 

[Bassmansam]

That gave the same result, replacing "" with 0 does work though.