Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
NewStep=let cols=Table.ColumNames(PreviousStepName) in PreviousStepName&#table(cols,{{null,null}&List.Transform(List.Skip(cols,2),each List.Sum(Table.Column(PreviousStepName,_)))})
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
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"),
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.
Regards
KT
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.