cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelsh
Advocate V
Advocate V

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 "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.

 

 

michaelsh_0-1621282742617.png

 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

10 REPLIES 10
watkinnc
Solution Specialist
Solution Specialist

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

 

watkinnc_0-1621358804974.png

 

edhans
Super User III
Super User III

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:

edhans_0-1621292452377.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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!

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

@michaelsh - was there anything else on this question?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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!

michaelsh_0-1621328170902.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors