Results 1 to 8 of 8

Thread: Adding columns of nested tables/ lists

  1. #1
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016

    Adding columns of nested tables/ lists



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Nesttable.JPG 
Views:	7 
Size:	44.5 KB 
ID:	10798  
    Attached Files Attached Files

  2. #2
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    I am not sure if I understood your requirement correctly, but from my point of view it is enough:

    PHP Code:
    let
        Source          
    Table.FromRows(Json.Document(Binary.Decompress
                          
    (Binary.FromText("i45W8krMU9JRciwoyEktBjIMDZRidWCi/kWJeelgYWMTJGH3osQCsKipKVjULTUJ2QhjZFGEEWYGSMJwI6CKfROLkjOQDTExQBFHGGOKKoEwCCgeCwA="
                          
    BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable textmeta [Serialized.Text true]) in type table [Month _tProduct _tSales _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" 

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,123
    Articles
    0
    Excel Version
    365
    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?

  4. #4
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by p45cal View Post
    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

  5. #5
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    23
    Articles
    0
    Excel Version
    2016
    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

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    In this case you only need:


    Code:
    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"

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,123
    Articles
    0
    Excel Version
    365
    Am I getting warm with the last step of Query2 in the attached?
    Attached Files Attached Files

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    210
    Articles
    0
    Excel Version
    2010
    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:

    Code:
    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"
    Out of many, one people

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •