FillDown is also doing unwanted row re-arranging

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,314
Reaction score
38
Points
48
Excel Version(s)
365
While developing a solution for here I'm creating a function which includes a FillDown step.
First I add a conditional column (MostRecentH4):
2020-06-03_092744.jpg
then I want to fill down, so I expect everything below the date in the top row to be filled with that date in the top row, right?
This is what I get:
2020-06-03_092938.jpg
The top two rows are swapped round first, then it's filled down!
Why!?
An earlier sort on another column has to be there as it cannot be assumed that the dates are in the right order in the source table.
Is there an explanation and or workaround?

In the attached file the function to be is called fnMiniTbl and it's the 4th step.
 

Attachments

  • msofficeforums44954Example mod_dev02.xlsx
    20.6 KB · Views: 10
Confirmed, but I have never seen this before and I use fill down a lot!
 
Unfortunately, this is a bit more common than we'd like to see. The issue is that a Sort step doesn't actually "lock" in place when you apply it, so certain actions just seem to ignore it.

If you need to lock your sort, then you can do it via a couple of methods:
  • Manually add a Table.Buffer in the next step to buffer the sort order, or
  • Add an Index Column after your sort, then remove the Index column immediately (make sure you get the Add Index Column and Remove Columns steps)

After either of those methods, the Fill Down should work properly.

FWIW, the Add/Remove Index Column triggers something in Power Query that acts like a Table.Buffer in some ways (although I don't think it's exactly the same.) We use that method in a few of our patterns.
 
Thank you Ken, now this is turning perverse; either adding a Table.Buffer line or adding then removing an index column immediately flip the top two rows, before even the FillDown line! Groan.

How I did the index add/removal:
Code:
    AddedConditionalColumn = Table.AddColumn(#"Sorted Rows", "MostRecentH4", each if = "H0004" then [Date] else null),
    #"Added Index" = Table.AddIndexColumn(AddedConditionalColumn, "Index", 0, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
(I did this one line higher too - same result).

How I did the table buffering:
Code:
#"Sorted Rows" = Table.Sort(myTable,{{"Date", Order.Ascending}}),
    zzz=Table.Buffer(#"Sorted Rows"),
    AddedConditionalColumn = Table.AddColumn(zzz, "MostRecentH4", each if = "H0004" then [Date] else null),
I tried buffering the table one line lower too - same result.

This has surely got to be a bug, if so how do I/we go about bringing it to the attention of the Power Query Team at Microsoft?
 
Last edited:
Unfortunately it won't get classified as a bug, but rather the way that PQ's Lazy Evaluation algorithm works. (Sorry, been there on this discussion.)

What appears to be happening is that - when you Sort the dates - it comes up with ties in the dates. It then tries to sort them based on the first column with a different value, and is sorting the numbers ahead of the text. The challenge you have here is that you can't assume that this data should be re-sorted, you want to lock down the table structure as is.

To work around this, I did this:
  • Added an Index after the myTable step
  • Sorted Rows based on date first, then the Index column second
  • Fill Down
  • Remove the Index column

At that point it seems to work for me. Usually I have to add that index to "lock" the table after the sort, but in this case it seems to be adding something to the sort, so it needs to be locked in earlier. (Sorry, I didn't look at your data earlier, so missed this.)
 
I'm very surprised that this isn't classified as a bug. I'm not really interested in how the sorting of ties takes place (although I'd have expected it to retain the sort order of the original unsorted table for this (I think Excel's sorting on sheets does this)) but that the sort, once done, is retained and not mucked about with afterwards, especially as this is such visual user interface. Re-arranging the sort behind the scenes without it being obvious to the user that it has done so is the bug. A sort, once done shouldn't change. 'Locking' should happen automatically. You shouldn't have to look out for this sort of nonsense.

In the event, for the real world solution I was trying for, it turned out that, for robustness, a secondary 'custom' sort on the Code column was needed anyway, so that tied dates would have the code H0004 row above any other Code:
Code:
#"Sorted Rows" = Table.Sort(myTable,{{"Date", Order.Ascending},{each List.PositionOf({"H0004"},[PLAIN][Code][/PLAIN]),Order.Descending}})
No re-sorting took place on fill down.
 
Last edited:
Back
Top