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
This will do it. It is largely what mahoneypat posted, but this keeps it as a list, never going back to a table.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjQyDH0EApVgcqbqxvYAQXN0WImyCpN0JSb4qsHiLuhGG+KULcGId6E0z3OGGYb4pivjGae2DmmKGJO+Ow1xnDXlOEOBZ/OWMzPxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Amt = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Amt", type number}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"AllRows", each Table.Sort(_,{"Date",Order.Ascending}), type table [Customer=nullable text, Date=nullable date, Amt=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
#"Added Custom1" =
Table.AddColumn(
#"Added Custom",
"NewTable",
each
let
varCurrentTable = [Custom]
in
Table.AddColumn(
[Custom],
"Running Total",
each
List.Sum(
List.FirstN(varCurrentTable[Amt], [Index])
)
)
)
in
#"Added Custom1"The issue is the List.FirstN cannot access the table you want directly, so you have to pass it as a variable.
I missed the part about not using List.Generate, so apologize for that, but I would strongly encourage you NOT to use this method for running totals. If you are just messing around exploring, this is fine, but if you are going to work with a large dataset this way, List.Generate scans your entire dataset 1 time. The method with List.Sum that incrementally adds new data at each row will scan the data the number of Customers by the number of rows added up. So in your data set:
| Customer | Rows | Total |
| A | 4 | 10 (1 + 2 + 3 + 4) |
| B | 6 | 21 |
| C | 4 | 10 |
| Total Rows Scanned | 41 |
vs 14 rows for List.Generate. On 14 rows it doesn't matter. On 10,000 rows it will.
Thanks, edhans !
You write "The issue is the List.FirstN cannot access the table you want directly, so you have to pass it as a variable."
Why is it so? (for educational purposes)
Thanks!
I think these articles might be helpful.
The Environment concept in M for Power Query and Power BI Desktop, Part 4 (ssbi-blog.de)
The Each Keyword in Power Query - The Excelguru BlogThe Excelguru Blog
Regards,
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.