Sum all individual Columns for large data sets

[Rick_S137]

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. 

[v-pgoloju]

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

[v-pgoloju]

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

[v-pgoloju]

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

[v-pgoloju]

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 kumar

[Rick_S137]

Hi 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?

[v-pgoloju]

Hi @Rick_S137,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

let
// Load and prepare sheet
Source = Excel.Workbook(File.Contents("C:\Users\v-pgoloju\Documents\sample file.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet1_Sheet, [IgnoreErrors=true]),

// Filter numeric columns that follow pattern like 'data_x_1'
ColumnNames = Table.ColumnNames(PromotedHeaders),
FilteredColumnNames = List.Select(ColumnNames, each Text.Contains(_, "_")),

// Safely sum numeric values from each column
ColumnSums = List.Transform(
FilteredColumnNames,
each [
OriginalName = _,
Sum = try List.Sum(List.RemoveNulls(List.Transform(Table.Column(PromotedHeaders, _), each if Value.Is(_, Number.Type) then _ else null))) otherwise 0
]
),

SumsTable = Table.FromRecords(ColumnSums),

// Extract group name (e.g., x, y, z)
AddGroupName = Table.AddColumn(
SumsTable,
"Group",
each Text.BetweenDelimiters([OriginalName], "_", "_"),
type text
),

// Group and summarize totals
GroupedResult = Table.Group(
AddGroupName,
{"Group"},
{{"Total", each List.Sum([Sum]), type number}}
),

// Pivot to final structure
FinalTable = Table.Pivot(
GroupedResult,
List.Distinct(GroupedResult[Group]),
"Group",
"Total"
)
in
FinalTable

 
I’ve tried to reproduce the scenario using the M code below. Please review and adjust it according to your data source. If the issue still persists, feel free to share more details, and we’ll be happy to assist further.

If this helps resolve your issue, kindly mark this response as the accepted solution and give it a thumbs-up to help others in the community as well.

Best regards,
Prasanna Kumar

[MarkLaf · Accepted solution]

I think the simplest approach for you is:

1. Leave your CV query as is, where it just focuses on querying data source files, parsing, combining, etc.
2. Right-click on your CV query, select Reference
   (this will create a new query where your first step, Source, is just pointing to CV)
3. 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
4. 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
    PivotValueAndSum

Here 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.

[Rick_S137]

I have tried using both sets of code you have provided, and these steps make sense. 

I am getting the same error for both sets of text which is:

[MarkLaf]

What is the earliest step that you see this error?

My guess is that somewhere you are trying to transform the column type from text or any to logical (True/False in UI), but when it tries to transform the value "(null)", it produces that error.

Since none of the M code suggested so far include a step like this, I suspect this is occurring upstream in your queries before we get to the particular transformation you asked for in OP.

[Rick_S137]

My Data starts as True and False with cells containing null. I had failed to remove one of my own steps changing type to a number 1's and 0's. 

[lbendlin]

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?

[Rick_S137]

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. 

[lbendlin]

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"  ?

[Rick_S137]

Yes, Name is a variable. There are different names in my data set, and there are also repeat names. In the case in which a name is repeated it needs to be summed and grouped with the column with which it shares the same name. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Rick_S137]

The data is now edited to match my data completely. 

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzclBUIZQDObG6kRjkUYXQVKF3TyIWbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [abc_X_123.stat.energize = _t, abc_X_456.stat.energize = _t, abc_X_789.stat.energize = _t, abc_Y_123.stat.energize = _t, abc_Z_123.stat.energize = _t, abc_Z_456.stat.energize = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Attribute.2", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Value] = "1")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

NOTE:  Your expected outcome is an anti-pattern.  This is Power BI, not Excel.  Leave the pivoting to the visuals.

[Rick_S137]

I don't fully understand how to use source code in power query, but I have a question about these lines, 

in type table [abc_X_123.stat.energize = _t, abc_X_456.stat.energize = _t, abc_X_789.stat.energize = _t, abc_Y_123.stat.energize = _t, abc_Z_123.stat.energize = _t, abc_Z_456.stat.energize = _t]),

 are these names hardcoded in, because these are not the actual names, and even if they were I have 200+ columns I cannot use any hardcoded mames as it would take me much to long to type all them in by hand.

I will still attempt to use this code in the meantime.

[lbendlin]

That is just the meta data of the sample data you provided. 

Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[Rick_S137]

I tried to use the source step but it doesnt work. I believe it is because of what I describe below

My initial source step is not the table I will be working with, power query seems to add steps automatically before hand. So do I add the Code from all the other steps as well?

I draw the data for my query from a folder, and use the "combine and transform" option, currently I have one CSV in the folder, but I will add the rest once I have the power query editing my data properly. I will also continually add data over time. 

Here are pictures of what my power query looks like when i load it for the first time with my data. I would include the function bar above the table, but it has sensitive text in it.