Forum Discussion
Column to List inside a nested table
A running total is done more easily in DAX and should be done there. However, one way to do it in the query editor is to use this as your last step in your query.
= Table.AddColumn(#"Added Custom", "NewTable", each let thistable = [Custom] in Table.AddColumn(thistable, "RT", each List.Sum(Table.FirstN(thistable, _[Index])[Amt])))
Pat
A running total is done more easily in DAX and should be done there. However, one way to do it in the query editor is to use this as your last step in your query.
= Table.AddColumn(#"Added Custom", "NewTable", each let thistable = [Custom] in Table.AddColumn(thistable, "RT", each List.Sum(Table.FirstN(thistable, _[Index])[Amt])))
Pat
Thanks, mahoneypat
As for DAX - I cannot do it there, since I have to continue with more Merges and Transformations. I am also working in Dataflows, so it is way before DAX.
But it's OK, your solution works great.
I changed it a bit, back to List.FirstN, but still, the key here is to use a variable as a table.
I wonder, why this syntax works: thistable[Amt], but this one doesn't: [Custom][Amt]: why can't I reference the table directly and need a variable? Just for educational purposes...
Also, why did you write _[Index] with underscore. Without _ it also works...
Thanks!