Forum Discussion
Sum all individual Columns for large data sets
Hello,
I am looking for a method to sum each individual column of a large data set, and group columns based on the name.
Example Data:
| abc_X_123.stat.energize | abc_X_456.stat.energize | abc_X_789.stat.energize | abc_Y_123.stat.energize | abc_Z_123.stat.energize | abc_Z_456.stat.energize |
| 1 | null | null | 1 | 1 | null |
| null | null | 1 | null | null | 1 |
| null | 1 | null | null | 1 | null |
(Bold and Italicized to make date more readable)
End Goal:
| X | Y | Z |
| 3 | 1 | 3 |
My main struggle with these transformations is finding a way to sum each individual column. The name part is easily handles by seperating by a delimiter.
My current approach has been to transpose the table, remove and delete the extra letters after the underscore, create a record transform the record to a list, sum the list, and delete all data except my sums and names. However this does not work because of the size of the data which essentially makes excel freeze after I transpose the table.
My next idea involved creating a duplicate query one containing column names only, which I can do without issue, and one containing only the data. On the table of data I then tried making a record into a list and using list.zip, which gives me the error:
Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=[Type]
Is there a different way to tackle this problem, it seems like most functions I find sum the row, which is not at all what I need.
I think the simplest approach for you is:
- Leave your CV query as is, where it just focuses on querying data source files, parsing, combining, etc.
- Right-click on your CV query, select Reference
(this will create a new query where your first step, Source, is just pointing to CV) - On your new query (rename to whatever), open the 'Advanced Editor', replace all text with a solution from here, just be sure that your Source step is referring to your original query, CV
- Most likely, you will want to turn off load on CV, so only the new table in desired output is added to your model and/or sheet
lbendlin's solution should work for you IF your columns are data type text and the only value you want to sum across all columns is "1" (as text).
In case your CV table has number/integer data typed columns, you can instead use the below code, following same approach, which is: 1) unpivot all columns, 2) extract group text between _'s, 3) pivot values back and sum
let // Reference to the original table Source = CV, // Unpivot all columns. Column names go to the "Attribute" column, // and values go to the "Value" column. UnpivotCols = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"), // Extract your group text/labels from between the first and second _ ExtractGroupText = Table.TransformColumns( UnpivotCols, {{"Attribute", each Text.BetweenDelimiters(_, "_", "_"), type text}} ), // Pivot the table back, summing all values under matching group labels PivotValueAndSum = Table.Pivot( ExtractGroupText, List.Distinct(ExtractGroupText[Attribute]), "Attribute", "Value", List.Sum ) in PivotValueAndSumHere is a quick gif of the steps I outlined at top. Note: this is in PBI Desktop, not Excel, so UI may be a little different, but everything should apply the same, mostly.
21 Replies
You are not summing all individual columns. Now you introduce column groups. Will they always have a single character and an underscore at the beginning of the column name?
The column names will follow this pattern: abc_Name_123.stat.energize, the only part of the column name I am interested in keeping is the part between the underscores.
After summing each indivdual column and prepping the names, I would use the Group By function to group and sum all columns name X. My main issue is finding a way to sum an each individual column. Again I am not sure if this is the most effective method, but it is what my initial thoughts and attempts were.
My first post was a poor description please disregard the information I provided in that post.
The column names will follow this pattern: abc_Name_123.stat.energize, the only part of the column name I am interested in keeping is the part between the underscores.so, "Name" ?
Hi Rick_S137,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Also, thanks to lbendlin for the prompt and helpful response.
Try this Combine and Transform" from a folder in Power Query, several steps are automatically generated—such as Filtered Hidden Files, Invoke Custom Function, Expanded Table Column, and Changed Type. These are designed to prepare and combine your CSV files into one table, so you shouldn’t place your custom column-summing logic in the Source step. Instead, insert a new step after the final auto-generated one (usually Changed Type) to apply your transformation to the fully combined data. This way, you avoid hardcoding column names and ensure the query continues to work as new files are added to the folder.
let
Source = #"Changed Type",
ColumnNames = Table.ColumnNames(Source),
ColumnSums = List.Transform(
ColumnNames,
each [
OriginalName = _,
Sum = List.Sum(List.RemoveNulls(Table.Column(Source, _)))
]
),
SumsTable = Table.FromRecords(ColumnSums),
AddGroupName = Table.AddColumn(
SumsTable,
"Name",
each Text.BetweenDelimiters([OriginalName], "_", "_")
),
GroupedResult = Table.Group(
AddGroupName,
{"Name"},
{{"Total", each List.Sum([Sum]), type number}}
)
in
GroupedResult
If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.Thank you & Regards,
Prasanna kumarHi Prasanna,
Can you describe to me the steps I would follow to add this to my power query.
I am trying to add it using the Fx button, as seen in the image below, is this the correct method or should I use another option?
I think the simplest approach for you is:
- Leave your CV query as is, where it just focuses on querying data source files, parsing, combining, etc.
- Right-click on your CV query, select Reference
(this will create a new query where your first step, Source, is just pointing to CV) - On your new query (rename to whatever), open the 'Advanced Editor', replace all text with a solution from here, just be sure that your Source step is referring to your original query, CV
- Most likely, you will want to turn off load on CV, so only the new table in desired output is added to your model and/or sheet
lbendlin's solution should work for you IF your columns are data type text and the only value you want to sum across all columns is "1" (as text).
In case your CV table has number/integer data typed columns, you can instead use the below code, following same approach, which is: 1) unpivot all columns, 2) extract group text between _'s, 3) pivot values back and sum
let // Reference to the original table Source = CV, // Unpivot all columns. Column names go to the "Attribute" column, // and values go to the "Value" column. UnpivotCols = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"), // Extract your group text/labels from between the first and second _ ExtractGroupText = Table.TransformColumns( UnpivotCols, {{"Attribute", each Text.BetweenDelimiters(_, "_", "_"), type text}} ), // Pivot the table back, summing all values under matching group labels PivotValueAndSum = Table.Pivot( ExtractGroupText, List.Distinct(ExtractGroupText[Attribute]), "Attribute", "Value", List.Sum ) in PivotValueAndSumHere is a quick gif of the steps I outlined at top. Note: this is in PBI Desktop, not Excel, so UI may be a little different, but everything should apply the same, mostly.
Hi Rick_S137,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.
Best regards,
Prasanna Kumar
Hi Rick_S137,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.
Warm regards,
Prasanna Kumar
Hi Rick_S137,
Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.
This not only closes the loop on your query but also helps others in the community solve similar issues faster.
Thank you for your time and feedback!
Best,
Prasanna Kumar
