Forum Discussion

jdusek92's avatar
jdusek92
Icon for Advocate III rankAdvocate III
7 years ago
Solved

Power Query - count across columns if condition met

Hello,  I have a query that has employees in rows and days (1-31) in columns = 31 additional columns - pivoted. These columns contain numbers from 0 to 3. I want to add new column that will return t...
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi jdusek92,

     

    You need to do some changes on your data model firstly.

     

    1. In Power Query, select columns 1,2,3,4,5 and click Unpivot columns.

    2. Filter rows with value >0

    3. Click Group by with Name

     

    In addition, you also could use Dax formula which may meet your desired output better.

     

    After Unpivot the columns 1,2,3,4,5, click Close&Apply, then create a calculated column with the formula below.

     

    count>0 =
CALCULATE (
    COUNT ( Table1[Attribute] ),
    FILTER (
        'Table1',
        'Table1'[Value] > 0
            && 'Table1'[Name] = EARLIER ( Table1[Name] )
    )
)

    Here is the output.

     

    More details, you could refer to this attachment. 

     

    Best  Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi jdusek92,

 

Based on your information, I'm still a little confused about your scenario.

 

If it is convenient, could you share some data sample and your desired output so that I can understand your scenario better and get the solution.

 

Best Regards,

Cherry

  • jdusek92's avatar
    jdusek92
    Icon for Advocate III rankAdvocate III
    7 years ago

     

     Hello, here is a little sample.

    I want to simulate the Excel Countifs formula in Power Query.

     

     Name1 2345Count of >0Excel formula
    John32032=4=COUNTIFS(Table1[@[1]:[5]];">0")
    Alice20033=3=COUNTIFS(Table1[@[1]:[5]];">0")
    Jacob12300=3=COUNTIFS(Table1[@[1]:[5]];">0")

     

    Warm regards

    Jakub

    • v-piga-msft's avatar
      v-piga-msft
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      Hi jdusek92,

       

      You need to do some changes on your data model firstly.

       

      1. In Power Query, select columns 1,2,3,4,5 and click Unpivot columns.

      2. Filter rows with value >0

      3. Click Group by with Name

       

      In addition, you also could use Dax formula which may meet your desired output better.

       

      After Unpivot the columns 1,2,3,4,5, click Close&Apply, then create a calculated column with the formula below.

       

      count>0 =
CALCULATE (
    COUNT ( Table1[Attribute] ),
    FILTER (
        'Table1',
        'Table1'[Value] > 0
            && 'Table1'[Name] = EARLIER ( Table1[Name] )
    )
)

      Here is the output.

       

      More details, you could refer to this attachment. 

       

      Best  Regards,

      Cherry