Forum Discussion
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 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
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.
4 Replies
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.
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 ForumAccumulateThe 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.
