Column to List inside a nested table

[michaelsh]

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 "Custom" column?

I understand that Table.Column can do this, but what is the correct syntaxt to reference the "Amt" column?

I went through lots of posts. I do have this working with function and with List.Generate, so please don't propose these answers.

I just want to know how can I turn a Column to a List inside a nested table.

Attached is the file

https://1drv.ms/u/s!AoP_9ampPIT7gZRy6370a-Wvg_TbwQ?e=PcTFSf

Thank you.

[watkinnc]

I'm not ssure if you want a single value in each list, or the whole column in each list, but here is the syntax for nested list column with single value in each list:

= Table.AddColumn(#"Added Custom", "New Table", each Table.AddColumn([Custom], "Running Total", each {[Amt]}))

--Nate

[edhans]

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.

[michaelsh]

@edhans 

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

[edhans]

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. 

[michaelsh]

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!

[edhans]

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.

[edhans]

@michaelsh - was there anything else on this question?

[mahoneypat]

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

[mahoneypat · Accepted solution]

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

[michaelsh]

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!