Forum Discussion
Solved
Column to List inside a nested table
Background: I want to calculate a Running Total by Group without using a function. I need help with just one little issue: How do I convert an "Amt" column to a List inside a nested table in a "Cus...
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
Think of it as EARLIER() in DAX michaelsh. It is one level up in the where the environment that List.FirstN is. So by capturing it as a variable in the Table.AddColumn() function it is accessible to other steps in the transformation. I use variables all of the time for this.
If you have a satisfactory answer michaelsh please mark one or more as the solution so this thread can be known to be solved.