Sorting with blank cells with excel/ power query

Bassmansam · 19 Sep 2022 at 17:11

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?

gpuerrilla · 19 Sep 2022 at 20:33

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.

Bassmansam · 20 Sep 2022 at 13:23

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.

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 · 20 Sep 2022 at 13:30

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.

Bassmansam · 20 Sep 2022 at 14:04

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.

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.

gpuerrilla · 20 Sep 2022 at 14:24

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

Bassmansam · 20 Sep 2022 at 14:29

gpuerrilla said:
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

gpuerrilla · 20 Sep 2022 at 17:10

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

Bassmansam · 20 Sep 2022 at 17:25

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

No rush man

gpuerrilla · 20 Sep 2022 at 17:33

How about a helper column?

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

Code:

=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.

Bassmansam · 20 Sep 2022 at 19:35

gpuerrilla said:
How about a helper column?

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

Code:

=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?

gpuerrilla · 20 Sep 2022 at 19:47

Yeah it references the cell your actual is in.

Bassmansam · 20 Sep 2022 at 20:04

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