Forum Discussion
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...
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.
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.
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