Forum Discussion
Sum all individual Columns for large data sets
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,
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
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?
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.
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:
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.
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