Results 1 to 6 of 6

Thread: FillDown is also doing unwanted row re-arranging

  1. #1
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365

    FillDown is also doing unwanted row re-arranging



    Register for a FREE account, and/
    or Log in to avoid these ads!

    While developing a solution for here I'm creating a function which includes a FillDown step.
    First I add a conditional column (MostRecentH4):
    Click image for larger version. 

Name:	2020-06-03_092744.jpg 
Views:	12 
Size:	30.5 KB 
ID:	9834
    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:
    Click image for larger version. 

Name:	2020-06-03_092938.jpg 
Views:	13 
Size:	26.1 KB 
ID:	9835
    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.
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    Confirmed, but I have never seen this before and I use fill down a lot!
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2020-06-03 at 06:55 PM.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    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"},[Code]),Order.Descending}})
    No re-sorting took place on fill down.
    Last edited by p45cal; 2020-06-03 at 08:42 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •