Adding columns of nested tables/ lists

[masterelaichi]

Hi,

I am trying to profile some tables I have in an sql database. The scenario is there is a column containing nested tables and I want to sum a specific column from each of the table and add it as a new column to the outer table

However, when I try to sum the column of the inner table I get this error "We cannot apply operator - to types Text and Text". I know this is due to a dataype of the sales column of the inner table. I have deliberately set this to text to replicate the datatype in my sql table.

Basically, I just want to add an extra column to the outer table that contains the sales for each month. In my actual dataset, I need to add different columns based on the filenames

I have attached a screenshot and the sample excel file if it helps

Thank you

[pinarello]

I am not sure if I understood your requirement correctly, but from my point of view it is enough:

let
    Source          = Table.FromRows(Json.Document(Binary.Decompress
                      (Binary.FromText("i45W8krMU9JRciwoyEktBjIMDZRidWCi/kWJeelgYWMTJGH3osQCsKipKVjULTUJ2QhjZFGEEWYGSMJwI6CKfROLkjOQDTExQBFHGGOKKoEwCCgeCwA=", 
                      BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Product = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Product", type text}, {"Sales", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"SQLrowcount", each Table.RowCount(_), Int64.Type}, {"total sales", each List.Sum([Sales]), type nullable number}})
in
    #"Grouped Rows" 

[p45cal]

Agree with pinarello, in the second step (Changed Type) instead of changing the Sales column to type text, make it a (decimal) number type. If you're wanting (initially) to have the column as text to replicate your sql data, then surely just add another Change Type to text - and if you want to keep two versions of the Sales column (text and number) then duplicate the Sales column first?

[masterelaichi]

Agree with pinarello, in the second step (Changed Type) instead of changing the Sales column to type text, make it a (decimal) number type. If you're wanting (initially) to have the column as text to replicate your sql data, then surely just add another Change Type to text - and if you want to keep two versions of the Sales column (text and number) then duplicate the Sales column first?

Yes, I understand changing the data type of the Sales column at an earlier step will give my required output. But given my scenario, I am trying to hard code it into the M code. My actual scenario is somewhat like this

file1	Table1	Sum Sales column from table1
file2	Table2	Sum qty column from table2
file3	Table3	Sum rate column from table3

Each of the three nested table have different structure

[masterelaichi]

thanks pinarello,

This was something I did try to to get the M code. Basically, I want add a custom column to sum up a specific column from the inber table, similar to the way I have added a custom column to count the number of rows in each table

[pinarello]

In this case you only need:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRciwoyEktBjIMDZRidWCi/kWJeelgYWMTJGH3osQCsKipKVjULTUJ2QhjZFGEEWYGSMJwI6CKfROLkjOQDTExQBFHGGOKKoEwCCgeCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Product = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Product", type text}, {"Sales", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"Count", each _, type table [Month=nullable text, Product=nullable text, Sales=nullable number]}, {"SqlRowCpount", each Table.RowCount(_), Int64.Type}, {"SalesSum", each List.Sum([Sales]), type nullable number}})
in
    #"Grouped Rows"

[p45cal]

Am I getting warm with the last step of Query2 in the attached?

[JoePublic]

If you want to be able to specify which column, I'd create a function for that that takes a table and column name as arguments - something like:

let
    SumTableColumn = (t as table, columnName as text) as number =>
        let
            TempSource = Table.TransformColumnTypes(t,{{columnName, type number}}),
            Result = List.Sum(Table.Column(TempSource, columnName))
        in
            Result,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRciwoyEktBjIMDZRidWCi/kWJeelgYWMTJGH3osQCsKipKVjULTUJ2QhjZFGEEWYGSMJwI6CKfROLkjOQDTExQBFHGGOKKoEwCCgeCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Product = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Product", type text}, {"Sales", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"Count", each _, type table [Month=nullable text, Product=nullable text, Sales=nullable number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "SQLrowcount", each Table.RowCount([Count])),
    #"Added Custom" = Table.AddColumn(#"Added Custom2", "Custom", each [Count][Sales]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each SumTableColumn([Count], "Sales") as number),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "ToList"}, {"Custom.1", "Totals"}})
in
    #"Renamed Columns"