Dynamic Percentage Columns in Power Query - No Hardcoding needed!
- V E Meganathan
- 11 hours ago
- 3 min read
Have you ever wondered how to calculate percentages across multiple columns without writing repetitive codes. In this post, i will show you a smart power query trick using
List. Accumulate.
Here's a dataset for product sales across four months. Each row represents product and each column represents sales numbers. I will use this dataset to dynamically add percentage columns for every numeric field in our table.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = List.Accumulate(List.Skip(Table.ColumnNames(Source)), Source, (s,c) => Table.AddColumn(s, c & " %", each Record.Field(_,c)/ List.Sum(List.Skip(Record.ToList(_))),Percentage.Type))
in
Result
Step-1: Extract Column Names.
We will begin by pulling out all column names from the table using Table. ColumnNames function. This gives us a list of column names to work with.
Since our focus is on numeric fields, we don't need product column. To exclude it, we will apply List. Skip function, which removes first item in the list - leaving us only the numeric column names.
Step 2: Inputs for List.Accumulate:
The number of new columns we’ll create is determined by the count of items in the column name list. Each of those new columns will be added back into the source table.
These two elements — the list of column names and the source table itself — serve as the inputs for the List.Accumulate function.
Step 3: The Accumulator Function Inside the List.Accumulate call, we define the function argument as (s, c) => ….
s represents the evolving table during iteration — it starts as the source table and grows as new columns are added.
c refers to the current item from the list of column names at each step.
We then use Table.AddColumn to add a new column to s.
For the new column name, we take the current item c and join "%" to it.
Inside the column definition, we begin with each to indicate row context.
Initially, we can place a simple value like 1 as a placeholder.
The actual transformation logic will be developed separately and then inserted here.
Step 4: Computing the Percentage:
To calculate a percentage, we need both a numerator and a denominator.
For example, in the first column (Jan), the numerator is the January sales for Product A.
The denominator is the total sales across all months for Product A.
Inside Table.AddColumn, the keyword each _ represents the current row as a record.
To extract January sales, we use Record.Field:
Record.Field(_, "Jan")
This returns 180, which is our numerator.
Again, _ is the current row record. To get all values, we convert it into a list using Record.ToList.
That list includes the product name as its first item. To exclude it, we apply List.Skip, leaving only the numeric sales values.
Finally, we use List.Sum to total those values. This gives us the denominator — the total sales for Product A across all months.
Step 5: Assigning Data Types and Finalizing the Transformation:
The fourth argument in Table.AddColumn allows us to specify the data type for the new column. Here, we’ll feed in Percentage.Type so that each added column is formatted correctly.
Next, instead of leaving the placeholder each 1 from our earlier step, we copy in the actual transformation logic — the numerator divided by the denominator.
Finally, rather than hardcoding "Jan", we replace it with the variable c, which dynamically represents the current column name during iteration.
With this setup, the query automatically adds four new percentage columns, each with:
Dynamic names (e.g., Jan %, Feb %, Mar %, Apr %)
Correct percentage values
Proper data type formatting