Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
ForumAccumulate
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.