List.Accumulate with conditional field

[JamesMcEwan]

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 below should result in a ItemID list of {1,2,3,4,5,9,10}:

[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}]

Here is a working table orders, each containing the order nested table with the ItemID lists. the RETURN column is what I wish to see. 

EDIT: I should add that the ItemID can be alphanumeric. 

Hope this makes sense. 

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}]
            }
    )
    
in
    Source

[artemus]

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]

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 · Accepted solution]

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]

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