none
Why would "Table.ExpandColumn" change values of the expanded table? RRS feed

  • Question

  • I have a table with "detail" status for each record. We are attempting to create a "Summary Status" by doing the following:

    1. Final Table with Subledger and "Detail" Status.
    2. Filter just the SubLedger and Status columns.
    3. Create a "Rank" that puts all the "open" status for each SubLedger at the top. (Open=1, Closed=2, Rejected=3, anything else=4)
    4. Sort on SubLedger + Status (puts "open" status at top)
    5. Removes Duplicates (Idea here if any member of a SubLedger status is "Open" they all should be Open.
    6. Merge Step 5 back into Step 1 to get a "summary" status for each Subledger.

    This works great up to the point where I use "Table.ExpandColumn" to expand the merged columns.  Here's the screen shot of BCC2061 with the "Status' column and the "SLStatus" ready to be expanded.  All recoreds (lines 124 - 127) have BCC2061 showing "Open" status which is correct as shown below. 

    But when I expand the table, for some mysterious reason, they all get changed to "Closed" as shown here:

    I have tried all sorts of things and just can't seem to figure out what could be causing this.  Here's a exert of the M Code starting from Step 1.

    ...    
        RemoveDashInSubLedger = Table.ReplaceValue(ReplaceNullTextField,"-","",Replacer.ReplaceText,{"SubLedger"}),
        AddField_OldStatusRank = Table.AddColumn(RemoveDashInSubLedger, "OldStatusRank", each if [Status]="Open" then 1 else
            if [Status]="Closed" then 2 else
            if [Status]="Rejected" then 3 else 4, Int64.Type),
        SortOn_SLandSLRank = Table.Sort(AddField_OldStatusRank,{{"SubLedger", Order.Ascending}, {"OldStatusRank", Order.Ascending}}),
        _RenameStatusDetail = Table.RenameColumns(SortOn_SLandSLRank,{{"Status", "StatusX"}}),
        _FilterOnlySLandStatus = Table.SelectColumns(_RenameStatusDetail,{"SubLedger", "StatusX"}),
        _AddField_StatusRank = Table.AddColumn(_FilterOnlySLandStatus, "Status Rank", each 
            if [StatusX]="Open" then 1 else
            if [StatusX]="Closed" then 2 else
            if [StatusX]="Rejected" then 3 else 4, Int64.Type),
        _SortOn_SLandRank = Table.Sort(_AddField_StatusRank,{{"SubLedger", Order.Ascending}, {"Status Rank", Order.Ascending}}),
        _RemoveRank = Table.RemoveColumns(_SortOn_SLandRank,{"Status Rank"}),
        _RemoveDupSL = Table.Distinct(_RemoveRank, {"SubLedger"}),
        _FilterBlanks = Table.SelectRows(_RemoveDupSL, each [SubLedger] <> null and [SubLedger] <> ""),
        _RenameNewStatus = Table.RenameColumns(_FilterBlanks,{{"StatusX", "Status"}}),
        _MergeTables = Table.NestedJoin(SortOn_SLandSLRank,{"SubLedger"},_RenameNewStatus,{"SubLedger"},"SLStatus",JoinKind.LeftOuter),
        _ExpandSLStatus = Table.ExpandTableColumn(_MergeTables, "SLStatus", {"Status"}, {"SLStatus.Status"}),
        _RemoveSLRank = Table.RemoveColumns(_ExpandSLStatus,{"OldStatusRank"})
    in
        _RemoveSLRank

    Any suggestions or assistance would be appreciated.

    Thanks!


    John Thomas

    Monday, March 19, 2018 6:53 PM

Answers

All replies

  • Hi John,

    please try buffering you sort-step like this:

    _SortOn_SLandRank = Table.Buffer(Table.Sort(_AddField_StatusRank,{{"SubLedger", Order.Ascending}, {"Status Rank", Order.Ascending}})),


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by jbt_PwrPvt Tuesday, March 20, 2018 5:24 PM
    Monday, March 19, 2018 8:45 PM
    Moderator
  • This does look wrong, yes. Do you have a self-contained (and non-sensitive-data) repro you can send us?
    Tuesday, March 20, 2018 12:38 PM
  • I'm pretty sure that the buffer will solve the issue, see this post for example: http://www.excelando.co.il/en/powerquery-remove-duplicates-bug-workaround/

    It is by design, that the sort order will mostly NOT be kept when the sort command is not the last step (see the answer from Tristan here: https://social.technet.microsoft.com/Forums/WINDOWS/en-US/30e9ab27-23a5-465b-a0c4-36e4e48ce2db/bug-or-feature-when-querying-sqlserver-data?forum=powerquery ), so you will always have to buffer your sorting if you want it to stick in within a query (unfortunately).


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by jbt_PwrPvt Tuesday, March 20, 2018 5:25 PM
    Tuesday, March 20, 2018 3:45 PM
    Moderator
  • I see; I missed the part where the distinct operation was being used to pick one value. Imke is right that distinct is not order-preserving.

    There seems to be an epidemic this week of me not reading things closely enough :/.

    • Marked as answer by jbt_PwrPvt Tuesday, March 20, 2018 5:26 PM
    • Unmarked as answer by jbt_PwrPvt Tuesday, March 20, 2018 5:26 PM
    Tuesday, March 20, 2018 4:52 PM
  • Imke - This did the trick.  I don't fully understand "why" exactly, but it seems to be working.  Seems to me once a relationship is established to a "distinct" record set, there should be no way for this to go off the rails.  I guess it's reading an "old" copy of the data before I made it a distinct list.

    If there is a better / easier way to accomplish the same thing, I am all ears!  It's the only one I could think of... 

    Thanks again...


    John Thomas


    • Edited by jbt_PwrPvt Tuesday, March 20, 2018 5:30 PM
    Tuesday, March 20, 2018 5:28 PM
  • Thanks for the clarification Curt!

    Any chance that helpful infos like this will make it into the official documentation ? ;-)

    I've started a repo on GitHub "Caveats in native M-functions" where I added some documentation-categories as a suggestion ( https://github.com/ImkeF/Caveats-in-native-M-functions/tree/master/Table.Sort ), but of course would prefer so see sth like this in the official documentation. 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, March 20, 2018 6:55 PM
    Moderator
  • I thought that I definitely seen this notification about order of rows etc in docs... and found it in the paragraph 3.5 and 3.6 of Language Specification. Not directly, of course, but this is the best I can find in the docs

    Maxim Zelensky Excel Inside

    Wednesday, March 21, 2018 5:14 PM