Count How Many Times a Value Appears in a Row Using Power Query

Problem:

Consider the following sample data:

I want to populate a Total Coupons Used column with the count of TRUE values that appeared for that customer in the Used [item] Coupon columns. So CustomerID 1 should have a count of 1, CustomerID 4 should have a count of 3, CustomerID 6 should have a count of 0 — you get the idea.

Let’s see how to do this in Power Query.

Solutions

We’re going to look at two solutions:

• Solution 1 that uses minimal M formula language and is a more naive, “hacky” solution.
• Solution 2 that uses more M formula language but is a cleaner, more elegant solution.

Solution 1: The naive or “hacky” way

Disclaimer: By describing this solution as naive or hacky, I don’t mean to imply that it can’t be used. The most important thing is for a solution to work.

Step 1: Get the data into Power Query

It should look something like this:

Step 2: Make duplicates of the Used Coupon columns

Right-click each Used Coupon column, and select Duplicate Column:

After duplicating each column, the new columns should look like this:

They can be renamed if you want, but it’s not that important as we will be removing them later.

Step 3: Replace the values in the duplicate columns

In order to sum the amount of coupons used, we need to replace TRUE values with 1, and FALSE values with 0.

Typically, we would do this by holding down CTRL or SHIFT, selecting all the duplicate columns, right-click, and choosing Replace Values:

However, since the data type of these columns are logical, Power Query gives an error that doesn’t allow us to replace it with an integer:

If you aren’t looking to count logical values but instead some other data type, then you might could use Replace Values. But for this example, it won’t work.

Instead of doing it that way, there’s an even easier way. Hold down CTRL or SHIFT, select all the duplicate columns, right-click, choose Change Type > Whole Number:

And just like that, we have 1s and 0s:

Step 4: Add custom column to sum the duplicate columns

From the tool bar, choose Add Column > Custom Column. Then, enter in something like this to sum the duplicate columns:

Click OK.

Our Total Coupons Used column is now populated:

Step 5: Remove the duplicate columns

The duplicate columns were just helper columns that we no longer need. Hold down CTRL or SHIFT, select all the duplicate columns, right-click, choose Remove Columns:

That’s it! We now have the count of coupons used:

Solution 2: The cleaner, more elegant way

For this solution, I’m going to show how to do it in this section, but I added a section after it to explain how the code works.

Step 1: Get the data into Power Query

It should look something like this:

Step 2: Add custom column to sum the count of TRUE values

From the tool bar, choose Add Column > Custom Column. Then, this is the logic needed for the custom column:

List.Sum(
   List.Transform(
      Record.FieldValues(
         Record.SelectFields(_, 
            {
             "Used Hamburger Coupon",
             "Used Chicken Tender Coupon",
             "Used Fries Coupon",
             "Used Drink Coupon"
            }
         )
      ), 
      each if _ = true then 1 else 0
   )
)

It will look something like this in the Custom Column editor:

Click OK.

That’s it! We now have the count of coupons used:

If you don’t want to see the explanation of this solution, then this is all you need. Thanks for reading!

If you want to see a breakdown of how it works, keep reading.

Solution 2 Explanation

Step 1: Select Fields

In the nested logic for the Total Coupons Used column in Solution 2, here is the first function being used:

Record.SelectFields(_, 
            {
             "Used Hamburger Coupon",
             "Used Chicken Tender Coupon",
             "Used Fries Coupon",
             "Used Drink Coupon"
            }
         )

For my understanding, I look at a Record as similar to a Row. I’m sure there’s a better technical explanation for it, but it’s how my brain interprets it.

For each row, we want to select the Used Coupon columns, and create a Record/Row just of those columns. That is why we pass the _ character, which can be considered like an iterator for each row of the table.

Let’s create a custom column using this logic and see what happens:

Click OK.

We now have a column where every row is a record.

If we select one of the rows in this column, we can see what the record looks like:

It works like expected. Each row is a record of the four columns we want to use in our solution.

Step 2: Get Field Values

In the nested logic for the Total Coupons Used column in Solution 2, here is the second function being used:

Record.FieldValues(
   Record.SelectFields(_, 
      {
       "Used Hamburger Coupon",
       "Used Chicken Tender Coupon",
       "Used Fries Coupon",
       "Used Drink Coupon"
      }
    )
)

This step is getting the values from the record that we created in Step 1. Let’s create a custom column using this logic and see what happens:

We are referencing the column created in the previous step since we created our record there.

Click OK.

We now have a column where every row is a list:

If we select one of the rows in this column, we can see what the list looks like:

It works like expected. We have a list of the values of the four columns we want to use in our solution.

Step 3: Transform values in the list

In the nested logic for the Total Coupons Used column in Solution 2, here is the third function being used:

List.Transform(
    Record.FieldValues(
        Record.SelectFields(_, 
          {
           "Used Hamburger Coupon",
           "Used Chicken Tender Coupon",
           "Used Fries Coupon",
           "Used Drink Coupon"
          }
         )
     ), 
   each if _ = true then 1 else 0
)

This step is changing the values in the list that we created in Step 2. We are using another _ character as an iterator to loop through each item in the list. The transformation logic is simple: if the value in the list is TRUE, change it to 1, and if the value in the list is FALSE, change it to 0. Let’s create a custom column using this logic and see what happens:

We are referencing the column created in the previous step since we created our list there.

Click OK.

We now have a column where every row is a list:

If we select one of the rows in this column, we can see what the list looks like:

It works like expected. As you can see, we now have a list of numbers instead of TRUE/FALSE values for the four columns we want to use in our solution.

Step 4: Sum the values in the list

In the nested logic for the Total Coupons Used column in Solution 2, here is the fourth (and final) function being used:

List.Sum(
    List.Transform(
        Record.FieldValues(
           Record.SelectFields(_, 
              {
               "Used Hamburger Coupon",
               "Used Chicken Tender Coupon",
               "Used Fries Coupon",
               "Used Drink Coupon"
              }
           )
         ), 
        each if _ = true then 1 else 0
     )
  )

This step is summing the values from the list in the previous step. Let’s create a custom column using this logic and see what happens:

We are referencing the column created in the previous step since we created our list there.

Click OK.

We now have a column with the correct total of TRUE values:

And that’s it! Now, you can look back at Solution 2 to see that all we did was combine all of this logic into one step for a clean, elegant solution to count all the TRUE values.

I’m sure there’s even more ways you could count the number of times a value appears in a row, but either of these examples should work for your solution.

Thanks for reading!