Forum Discussion

JamesMcEwan's avatar
JamesMcEwan
Helper I
4 years ago
Solved

List.Accumulate with conditional field

Hi,    I am trying to use List.Accumulate on a list of Items, however the items should be added to the accumulate or removed from the accumulate depending on the TransactionType. For instance the b...
Need Help
  • artemus's avatar
    artemus
    4 years ago

    The issue is that [Order][TransactionType] is a list value, not a text value.  In your accumulator, you want to pass in Table.ToRecords([Order]), then use c[TransactionType] and c[ItemId] instead of [TransactionType] and c respectivly. Also, the & is the list concatnation operator.

artemus's avatar
artemus
Microsoft Employee
4 years ago

Your accumulate function would start with an empty list, and have an "if" that tests the TransationType. On TransationType = "Add", you would use List.Distinct(current & next) and on TransactionType = "Rem" you would use List.RemoveItems(current, next) where current and next are the first and second parameters to your accumulate function.

  • JamesMcEwan's avatar
    JamesMcEwan
    Helper I
    4 years ago

    Hi artemus,

     

    Thanks for the help. I think I get your answer conceptually and tried to impliment it over the table I have - however it the lists return zero records so I must be doing something wrong:

     

     

    let
    Source =    
        Table.FromRecords(
            {
                [CustomerID = 1, Name = "Bob", Order = 
                    Table.FromRecords(
                        {
                            [TransactionID = 1, LineID = 1, TransactionType = "Add", ItemID = {1..3}],
                            [TransactionID = 2, LineID = 2, TransactionType = "Add", ItemID = {4..7}],
                            [TransactionID = 3, LineID = 3, TransactionType = "Rem", ItemID = {6..8}],
                            [TransactionID = 4, LineID = 4, TransactionType = "Add", ItemID = {9..10}]
                        }
                    )
                , RESULT = {1,2,3,4,5,9,10}],
               [CustomerID = 2, Name = "Jim", Order =  
                    Table.FromRecords(
                        {
                            [TransactionID = 5, LineID = 1, TransactionType = "Add", ItemID = {1..4}],
                            [TransactionID = 6, LineID = 2, TransactionType = "Rem", ItemID = {3..6}],
                            [TransactionID = 7, LineID = 3, TransactionType = "Add", ItemID = {6..10}],
                            [TransactionID = 8, LineID = 4, TransactionType = "Rem", ItemID = {8..9}]
                        }
                    )
              , RESULT = {1,2,6,7,10}]
            }
    ),
    ForumAccumulate = 
        Table.AddColumn(Source, "ForumResult", 
            each List.Accumulate( 
                [Order][ItemID],
                {},
                (s,c) => 
                if [Order][TransactionType] = "Add" 
                then List.Distinct ( s & c ) 
                else List.RemoveItems ( s , c )
            )
        )
in
    ForumAccumulate

     

    • artemus's avatar
      artemus
      Microsoft Employee
      4 years ago

      The issue is that [Order][TransactionType] is a list value, not a text value.  In your accumulator, you want to pass in Table.ToRecords([Order]), then use c[TransactionType] and c[ItemId] instead of [TransactionType] and c respectivly. Also, the & is the list concatnation operator.

      • JamesMcEwan's avatar
        JamesMcEwan
        Helper I
        4 years ago

        Hi artemus,

         

        Fantastic - of course! Sometimes you can't see the forest for the trees. Below is the solution I used. 

         

        Thanks so much for your help!

         

        let
    Source =    
        Table.FromRecords(
            {
                [CustomerID = 1, Name = "Bob", Order = 
                    Table.FromRecords(
                        {
                            [TransactionID = 1, LineID = 1, TransactionType = "Add", ItemID = {1..3}],
                            [TransactionID = 2, LineID = 2, TransactionType = "Add", ItemID = {4..7}],
                            [TransactionID = 3, LineID = 3, TransactionType = "Rem", ItemID = {6..8}],
                            [TransactionID = 4, LineID = 4, TransactionType = "Add", ItemID = {9..10}]
                        }
                    )
                , RESULT = {1,2,3,4,5,9,10}],
               [CustomerID = 2, Name = "Jim", Order =  
                    Table.FromRecords(
                        {
                            [TransactionID = 5, LineID = 1, TransactionType = "Add", ItemID = {1..4}],
                            [TransactionID = 6, LineID = 2, TransactionType = "Rem", ItemID = {3..6}],
                            [TransactionID = 7, LineID = 3, TransactionType = "Add", ItemID = {6..10}],
                            [TransactionID = 8, LineID = 4, TransactionType = "Rem", ItemID = {8..9}]
                        }
                    )
              , RESULT = {1,2,6,7,10}]
            }
    ),
        ForumAccumulate = 
            Table.AddColumn(
                Source, 
                "ForumResult", 
                each List.Accumulate( 
                    Table.ToRecords([Order]),
                        {},
                        (s,c) => 
                            if c[TransactionType] = "Add" 
                            then List.Distinct ( s & c[ItemID] ) 
                            else List.RemoveItems ( s , c[ItemID] )
                )
            )
in
    ForumAccumulate