Insert Total Row for Dynamic Columns

[Jgilbe4]

Hi, I need to add a total row to the bottom of my table and my table has dynamic columns. After I have grouped my data and pivoted the columns, my table has two core columns on the left which never change- Presentation Name and Title. The columns to the right of the core columns are project numbers. These project numbers change periodically when a project ends or new ones are added.

So an example table might look like this:

Presentation Name Title 123-000 456-000

Bob                          VP    5,0000   10,000

Suzy                         HQ    1,000       2,000

I have tried to do the following but I get stuck on how to add a list sum formula for the dynamic columns:

Table.InsertRows(Previous Step, 0, {[Presentation Name=null, Title=null]}

The reason that I want to add a total row in Power Query is because I'm creating multiple files that are all alike for someone else to take over and I want to make sure that there is less work for her to update. Whenever I have a total row at the bottom of my table outside of Power Query,  if a project number changes, then I have to click and drag my sum formula each time. 

Any recommendations please?

[wdx223_Daniel]

NewStep=let cols=Table.ColumNames(PreviousStepName) in PreviousStepName&#table(cols,{{null,null}&List.Transform(List.Skip(cols,2),each List.Sum(Table.Column(PreviousStepName,_)))})

[KT_Bsmart2gethe]

Hi @Jgilbe4 ,

Please see below the step-to-step code for inserting total row:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRCgsAEqYGBgZAytAARMfqRCsFl1ZVAgU8AqGiQMoILBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Presentation Name " = _t, Title = _t, #"123-000" = _t, #"456-000" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"123-000", type number}, {"456-000", type number}}),
    
    
    Step1 = Table.ToColumns(#"Changed Type"),                               //turn each columns to list

    Step2 = List.Transform(
                Step1, 
                each 
                    List.Sum(
                        List.Select(_, each Value.Type(_) = type number)    //select only number value (change to currency type if is currency type)
                    )
                ), 
    Step3 = Record.FromList(Step2, Table.ColumnNames(#"Changed Type")),     //turn Step2 into a record for Step5

    Step4 = Table.RowCount(#"Changed Type"),                                //get the row number for Step5

    Step5 = Table.InsertRows(#"Changed Type", Step4, {Step3})               //insert total row
in
    Step5

You will need to ensure the data type for column sum columns is the same as Step2; otherwise you will get nothing.

Regards

KT

[Jgilbe4]

Thank you so much for replying to me! I still feel confused though. I understand what you mean about changing the column types for the project numbers however I did say that the project numbers are dynamic and change all the time. In the code above I am not sure how this is addressed. 

Would I need to have some code that looks like the following below added? And if so how would the code change that you sent me?

#"Core Columns"= {"Presentation Name, "Title"}
#"Dynamic Columns"= List.Difference(Tablr.ColumnNames(Source,#"Core Columns"),

[KT_Bsmart2gethe]

Hi @Jgilbe4 ,

The two lines of code you provided above didn't tell me much. Perhaps you can share your full m code, and then I can tell you where to join.

Alternatively, I have a link below that provides a dynamic change type.

Dynamic Changed Type 

Regards

KT