Forum Discussion

Rick_S137's avatar
Rick_S137
Helper I
1 year ago
Solved

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_7...
Need Help
  • MarkLaf's avatar
    MarkLaf
    1 year ago

    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's avatar
Rick_S137
Helper I
1 year ago

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?

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

 

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