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
I think if you use that method michaelsh it will bog down on larger datasets. Try this instead. I've used this on tables with over 100,000 records and it performs very well.
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}}),
BufferedCustomer = List.Buffer(#"Changed Type"[Customer]),
BufferedAmount = List.Buffer(#"Changed Type"[Amt]),
ListSize = List.Count(BufferedAmount),
RunningTotal =
List.Generate(
()=> [Total = BufferedAmount{0}, Counter = 0],
each [Counter] < ListSize,
each
try
if BufferedCustomer{[Counter] + 1} = BufferedCustomer{[Counter]}
then [Total = [Total] + BufferedAmount{[Counter] + 1}, Counter = [Counter] + 1]
else [Total = BufferedAmount{[Counter] + 1}, Counter = [Counter] + 1]
otherwise [Counter = [Counter] + 1],
each [Total]
),
CombinedColumns = Table.ToColumns(#"Changed Type") & {RunningTotal},
NewTable =
Table.FromColumns(
CombinedColumns,
Table.ColumnNames(#"Changed Type") & {"Running Total"}
)
in
NewTable
The result is:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
If you walk through the steps, you can see that the BufferedCustomer, BufferedAmount, and RunningTotal are just 3 different lists. It walks through BufferedCustomer and BufferedAmount one at a time and generates the Running Total list.
The CombinedColumns step adds the RunningTotal list to the list of all of your other columns, and finally NewTable puts it all back together. All you have to do is assign the data type at the end.
Thank you, but I explicitly asked not to give answers with functions and List.Generate, I already know them.
My question is simple: how do I convert a column in a nested table to a list.
Thank you
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.
