Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to calculate Power Query Percent of total or category

Power Query Percent of Total Featured Image

In this post, we look at how to calculate the percentage of a total in Power Query. But we will also take this one step further, to consider how to calculate the percent of a category.

In standard Excel, these calculations are simple because we are so used to the formulas to achieve this. For many, Power Query is still a newer tool, and it doesn’t operate in quite the same way. But once you’ve calculated a Power Query percentage of total, I think you will find is straightforward.

Download the example file: Click the link below to download the example file used for this post:

Power Query percent of total – quick answer

The data in our example looks like this; it is a list of cities where a company has offices. Each office has a region and a headcount value.

Original data

Our goal is to:

  • Calculate the % of total headcount at each site
  • Calculate the % of Region head count at each site

Percent of total in Power Query

To calculate the % of the total is reasonably straightforward. While the SUM function does not exist in Power Query, the List.Sum function does.

  1. Within Power Query click Add Column > Custom Column
    Add Custom Column - PQ formula
  2. In the Custom Column dialog box enter the following formula: =[Headcount] / List.Sum(#”Changed Type”[Headcount])
  3. Change the formula to fit your scenario:
    • [Headcount] is the name of the column for which you want to calculate the %
    • #”Changed Type” is the name of the step to be used as the source for the formula. Typically, this is the name of the previous step in the Advanced Editor window.
  4. Give the custom column a useful name, such as % of total, then click OK.
    Pct of total Custom Column dialog
  5. The % of total column is now included in the preview window.
    Preview window showing the added column
  6. It doesn’t look like a percentage yet, so change the data type by clicking on the ABC123 button and selecting Percentage from the menu.
    Change data type to percentage

That’s it, here is the final table:

Percent of total finished

That wasn’t too bad, was it, eh? The solution was very similar to how we would do it in Excel. Keep reading to understand more about how this calculation works.

Percent of category in Power Query

To calculate the % of a category, it’s not quite as easy. Power Query does not have the equivalent of the SUMIF or SUMIFS functions, so we need to think differently. Instead, we create a transformation formula to achieve the same result.

  1. Within Power Query click Add Column > Custom Column
    Add Custom Column - PQ formula
  2. In the Custom Column dialog box enter the following formula:
    =[Headcount] / Table.SelectRows( Table.Group(#”Changed Type”, {“Region”}, {{“Category Total”, each List.Sum([Headcount]), type number}}) , each ([Region] = [Region])){[Region=[Region]]}[Category Total]
  3. Change the formula to fit your scenario:
    • #”Changed Type” is the name of the step to be used as the source for the formula. Typically, this is the name of the previous step in the Advanced Editor window
    • [Region] or “Region” is the name of the column which contains the field to categorize by
    • [Headcount] is the name of the column for which you want to calculate the %
  4. Give the custom column a useful name, such as % of category, then click OK.
    Custom column dialog for percent of category
  5. The % of category column ins included in the preview window.
  6. Finally, change the data type to percentage.

Here is the final table:

Finished table with percent by category

The process was similar to calculating the percent of a total, but the formula you pasted is much more complex. In the sections below, we’ll dig a bit deep to understand how it works. This will enable you to create this transformation for yourself.

How % of total works

Power Query, itself does not have a total row. This isn’t a problem, as the tool is intended for data manipulation, rather than presentation. But it does mean there is not a total row to divide by.

List.Sum is similar to Excel’s SUM function. The following M code uses that formula to calculate the total value for the Headcount column, using the #”Changed Type” step as its source.

List.Sum(#"Changed Type"[Headcount])

Having calculated the total, we just need to divide the number in each row of the headcount column by the result of the List.Sum function result.

=[Headcount] / List.Sum(#"Changed Type"[Headcount])

Pretty easy, right?

Learn more about the List.Sum function here: https://bioffthegrid.com/list-sum

How % of category works

Calculating the % of a category is a little tricky. We have seen this scenario previously when we looked at custom functions. While a custom function is an option, we can also achieve the result (and possibly easier) by nesting transformations into a single formula.

We will step through the transformations one-by-one so that we can understand how the technique works.

Step 1

Begin with the source table loaded into Power Query.

Original data

Step 2

Click Transform > Group By from the Power Query ribbon.

Transfrom - Group By

The Group By dialog box opens. Enter the following information:

  • View: Basic
  • Column: Region (i.e., the column which contains the category column)
  • New column name: Any name you want. Given our data set, Total Region seems sensible
  • Operation: Sum – the calculation we want to perform
  • Column: Headcount – the column containing the numbers we want to perform the operation on
Group By window showing options

Click OK to close the dialog box.

Look at the formula bar at the top of the Preview Window (click View > Formula Bar if it is not visible). The M code for this step looks like this:

= Table.Group(#"Changed Type", {"Region"}, 
{{"Total Region", each List.Sum([Headcount]), type number}})

The Table.Group function is the M code which executes the Group By transformation. Learn more about the Table.Group function here: https://docs.microsoft.com/en-gb/powerquery-m/table-group

Step 3

Filter the category column to include a single value. I have selected Wales. Then click OK.

Filter and select item

The M code for this step is:

= Table.SelectRows(#"Grouped Rows", each ([Region] = "Wales"))

The Table.SelectRows function is the M code that executes a column filter. Learn more about the Table.SelectRows function here: https://docs.microsoft.com/en-gb/powerquery-m/table-selectrows

Step 4

Now let’s combine the two transformations from Step 2 and Step 3 into a single formula.

#”Grouped Rows” is the reference to the step above. So, we just need to make two simple changes:

  • Add the text before the step name to the beginning of the step above
  • Add the text after the step name to the end of the step above

The M code now looks like this:

=Table.SelectRows(
Table.Group(#"Changed Type", {"Region"}, 
{{"Total Region", each List.Sum([Headcount]), type number}})
, each ([Region] = "Wales"))

The different sections are:

  • The transformation from Step 2
  • The code added from Step 3

As the last step has now been added into the step above it is not longer required, we can delete the last step.

The preview window now only has one line of data.

Preview window displaying just one line

Step 5

Right-click on the value within the Total Region column and select Drill Down from the menu.

Drill Down into value

The M code for this step looks like this:

= #"Grouped Rows"{[Region="Wales"]}[Total Region]

Step 6

Now let’s add the code from step 5 into Step 4.

The M code now looks like this:

=Table.SelectRows(
Table.Group(#"Changed Type", {"Region"}, 
{{"Total Region", each List.Sum([Headcount]), type number}})
, each ([Region] = "Wales")){[Region="Wales"]}[Total Region]

The sections are:

  • The transformation from Step 2
  • The added code from Step 3
  • The added code from Step 5

As the drill down has now been incorporated into the step above, we can delete the last step from the applied steps window.

Step 7

We now have all the transformations required, so it’s time to turn the formula into its own column.

  1. Copy all the text for the combined formula from the Formula Bar.
  2. Delete the step containing the formula from the Applied Steps list.
  3. Click Add Column > Custom Column
  4. Paste the copied text into the Custom Column dialog box.
    Custom column with M code copied in
  5. Replace each instance of “Wales” with [Region]. The code now looks like this: Obviously, you’ll adapt this to your scenario.
    =Table.SelectRows( Table.Group(#”Changed Type”, {“Region”}, {{“Total Region”, each List.Sum([Headcount]), type number}}) , each ([Region] = [Region])){[Region=[Region]]}[Total Region]
  6. Finally, add the column to be divided to the start of the formula =[Headcount] / Table.SelectRows( Table.Group(#”Changed Type”, {“Region”}, {{“Total Region”, each List.Sum([Headcount]), type number}}) , each ([Region] = [Region])){[Region=[Region]]}[Total Region]

That’s it; we now have a % of the region for every row.

Percent of total finished

Conclusion

In this post, we focused on how to calculate the percentage of a total in Power Query and also the percentage of a category. Through this, we learned about the List.Sum function (which is similar to Excel’s SUM function) and also how to combine queries into a single transformation step. This is a great technique to achieve more advanced transformations.

Related posts:

In this post, we look at how to calculate the percentage of a total in Power Query. But we will also take this one step further, to consider how to calculate the percent of a category.

In standard Excel, these calculations are simple because we are so used to the formulas to achieve this. For many, Power Query is still a newer tool, and it doesn’t operate in quite the same way. But once you’ve calculated a Power Query percentage of total, I think you will find is straightforward.

Download the example file: Click the link below to download the example file used for this post:

Power Query percent of total – quick answer

The data in our example looks like this; it is a list of cities where a company has offices. Each office has a region and a headcount value.

Original data

Our goal is to:

  • Calculate the % of total headcount at each site
  • Calculate the % of Region head count at each site

Percent of total in Power Query

To calculate the % of the total is reasonably straightforward. While the SUM function does not exist in Power Query, the List.Sum function does.

  1. Within Power Query click Add Column > Custom Column
    Add Custom Column - PQ formula
  2. In the Custom Column dialog box enter the following formula: =[Headcount] / List.Sum(#”Changed Type”[Headcount])
  3. Change the formula to fit your scenario:
    • [Headcount] is the name of the column for which you want to calculate the %
    • #”Changed Type” is the name of the step to be used as the source for the formula. Typically, this is the name of the previous step in the Advanced Editor window.
  4. Give the custom column a useful name, such as % of total, then click OK.
    Pct of total Custom Column dialog
  5. The % of total column is now included in the preview window.
    Preview window showing the added column
  6. It doesn’t look like a percentage yet, so change the data type by clicking on the ABC123 button and selecting Percentage from the menu.
    Change data type to percentage

That’s it, here is the final table:

Percent of total finished

That wasn’t too bad, was it, eh? The solution was very similar to how we would do it in Excel. Keep reading to understand more about how this calculation works.

Percent of category in Power Query

To calculate the % of a category, it’s not quite as easy. Power Query does not have the equivalent of the SUMIF or SUMIFS functions, so we need to think differently. Instead, we create a transformation formula to achieve the same result.

  1. Within Power Query click Add Column > Custom Column
    Add Custom Column - PQ formula
  2. In the Custom Column dialog box enter the following formula:
    =[Headcount] / Table.SelectRows( Table.Group(#”Changed Type”, {“Region”}, {{“Category Total”, each List.Sum([Headcount]), type number}}) , each ([Region] = [Region])){[Region=[Region]]}[Category Total]
  3. Change the formula to fit your scenario:
    • #”Changed Type” is the name of the step to be used as the source for the formula. Typically, this is the name of the previous step in the Advanced Editor window
    • [Region] or “Region” is the name of the column which contains the field to categorize by
    • [Headcount] is the name of the column for which you want to calculate the %
  4. Give the custom column a useful name, such as % of category, then click OK.
    Custom column dialog for percent of category
  5. The % of category column ins included in the preview window.
  6. Finally, change the data type to percentage.

Here is the final table:

Finished table with percent by category

The process was similar to calculating the percent of a total, but the formula you pasted is much more complex. In the sections below, we’ll dig a bit deep to understand how it works. This will enable you to create this transformation for yourself.

How % of total works

Power Query, itself does not have a total row. This isn’t a problem, as the tool is intended for data manipulation, rather than presentation. But it does mean there is not a total row to divide by.

List.Sum is similar to Excel’s SUM function. The following M code uses that formula to calculate the total value for the Headcount column, using the #”Changed Type” step as its source.

List.Sum(#"Changed Type"[Headcount])

Having calculated the total, we just need to divide the number in each row of the headcount column by the result of the List.Sum function result.

=[Headcount] / List.Sum(#"Changed Type"[Headcount])

Pretty easy, right?

Percent of total finished

Learn more about the List.Sum function here: https://bioffthegrid.com/list-sum

How % of category works

Calculating the % of a category is a little tricky. We have seen this scenario previously when we looked at custom functions. While a custom function is an option, we can also achieve the result (and possibly easier) by nesting transformations into a single formula.

We will step through the transformations one-by-one so that we can understand how the technique works.

Step 1

Begin with the source table loaded into Power Query.

Original data

Step 2

Click Transform > Group By from the Power Query ribbon.

Transfrom - Group By

The Group By dialog box opens. Enter the following information:

  • View: Basic
  • Column: Region (i.e., the column which contains the category column)
  • New column name: Any name you want. Given our data set, Total Region seems sensible
  • Operation: Sum – the calculation we want to perform
  • Column: Headcount – the column containing the numbers we want to perform the operation on
Group By window showing options

Click OK to close the dialog box.

Look at the formula bar at the top of the Preview Window (click View > Formula Bar if it is not visible). The M code for this step looks like this:

= Table.Group(#"Changed Type", {"Region"}, 
{{"Total Region", each List.Sum([Headcount]), type number}})

The Table.Group function is the M code which executes the Group By transformation. Learn more about the Table.Group function here: https://docs.microsoft.com/en-gb/powerquery-m/table-group

Step 3

Filter the category column to include a single value. I have selected Wales. Then click OK.

Filter and select item

The M code for this step is:

= Table.SelectRows(#"Grouped Rows", each ([Region] = "Wales"))

The Table.SelectRows function is the M code that executes a column filter. Learn more about the Table.SelectRows function here: https://docs.microsoft.com/en-gb/powerquery-m/table-selectrows

Step 4

Now let’s combine the two transformations from Step 2 and Step 3 into a single formula.

#”Grouped Rows” is the reference to the step above. So, we just need to make two simple changes:

  • Add the text before the step name to the beginning of the step above
  • Add the text after the step name to the end of the step above

The M code now looks like this:

=Table.SelectRows(
Table.Group(#"Changed Type", {"Region"}, 
{{"Total Region", each List.Sum([Headcount]), type number}})
, each ([Region] = "Wales"))

The different sections are:

  • The transformation from Step 2
  • The code added from Step 3

As the last step has now been added into the step above it is not longer required, we can delete the last step.

The preview window now only has one line of data.

Preview window displaying just one line

Step 5

Right-click on the value within the Total Region column and select Drill Down from the menu.

Drill Down into value

The M code for this step looks like this:

= #"Grouped Rows"{[Region="Wales"]}[Total Region]

Step 6

Now let’s add the code from step 5 into Step 4.

The M code now looks like this:

=Table.SelectRows(
Table.Group(#"Changed Type", {"Region"}, 
{{"Total Region", each List.Sum([Headcount]), type number}})
, each ([Region] = "Wales")){[Region="Wales"]}[Total Region]

The sections are:

  • The transformation from Step 2
  • The added code from Step 3
  • The added code from Step 5

As the drill down has now been incorporated into the step above, we can delete the last step from the applied steps window.

Step 7

We now have all the transformations required, so it’s time to turn the formula into its own column.

  1. Copy all the text for the combined formula from the Formula Bar.
  2. Delete the step containing the formula from the Applied Steps list.
  3. Click Add Column > Custom Column
  4. Paste the copied text into the Custom Column dialog box.
    Custom column with M code copied in
  5. Replace each instance of “Wales” with [Region]. The code now looks like this: Obviously, you’ll adapt this to your scenario.
    =Table.SelectRows( Table.Group(#”Changed Type”, {“Region”}, {{“Total Region”, each List.Sum([Headcount]), type number}}) , each ([Region] = [Region])){[Region=[Region]]}[Total Region]
  6. Finally, add the column to be divided to the start of the formula =[Headcount] / Table.SelectRows( Table.Group(#”Changed Type”, {“Region”}, {{“Total Region”, each List.Sum([Headcount]), type number}}) , each ([Region] = [Region])){[Region=[Region]]}[Total Region]

That’s it; we now have a % of the region for every row.

Finished table with percent by category

Conclusion

In this post, we focused on how to calculate the percentage of a total in Power Query and also the percentage of a category. Through this, we learned about the List.Sum function (which is similar to Excel’s SUM function) and also how to combine queries into a single transformation step. This is a great technique to achieve more advanced transformations.

Related posts:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published.