Count number of multiple non-blank fields in a row

[flanshaw]

Hi, I have a data set that for every row has 20 procedure codes ie.

name, proc1,proc2,proc3,proc4,proc5,......,proc20

JR,H123, H321, ,, ,,, , 

the data set has around 1.2 million rows

Im trying to figure out how to create a custom column that adds up the number of fields per row that are not blank, on a row by row basis.

ie in the example above, the custom column would equal 2, as there are 2 procedure codes with non-blank entries

or another example:

DS, J123,U7345,P344,T546, , , , , , , ..... would equal 4

thanks

Any thoughts - is there  a DAX function that would do this for me.

[v-huizhn-msft · Accepted solution]

@flanshaw

From what I know, I find two solution to solve your issue. I test it using the name have 10 products rather than 20 products.

On the one hand, if the name row is unique, please create a calculated column using the following formula to get expected result.

Result = IF(ISBLANK(Table6[proc1]),0,1)+IF(ISBLANK(Table6[proc2]),0,1)+IF(ISBLANK(Table6[proc3]),0,1)+IF(ISBLANK(Table6[proc4]),0,1)+IF(ISBLANK(Table6[proc5]),0,1)+IF(ISBLANK(Table6[proc6]),0,1)+IF(ISBLANK(Table6[proc7]),0,1)+IF(ISBLANK(Table6[proc8]),0,1)+IF(ISBLANK(Table6[proc9]),0,1)+IF(ISBLANK(Table6[proc10]),0,1)

 

On the other hand, please use Unpivot Columns and Group by function in Power Query. You can transform selected columns into attribute-value pairs where columns become rows using “Unpivot Columns”. And aggregate row when select group by.

You can refer to below operation to get what you want.

1. I create the following sample data. Right Click->Edit Query, we will get the Edit Query interface in second screenshot.

2. Select the first column (name), on the Ribbon Transform, click on Unpivot Columns > Unpivot Other Columns(highlighted in yellow as follows). From the result, the first column is name, the second is the product type, and the third is the value of each type product, it will elliptic if the value is null.

 

3. Click the Group By, enter the name and select right operation, it will aggregate according to name like below screenshot.

4. Click OK, you will get the expected result. Click close and apply. The table will be changed based on operation. The first picture display the expected result in Edit Query, and the second picture displays the result table in PowerBI desktop.

For more details, please review the article. If you have any question, please feel free to ask.

Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

@flanshaw

From what I know, I find two solution to solve your issue. I test it using the name have 10 products rather than 20 products.

On the one hand, if the name row is unique, please create a calculated column using the following formula to get expected result.

Result = IF(ISBLANK(Table6[proc1]),0,1)+IF(ISBLANK(Table6[proc2]),0,1)+IF(ISBLANK(Table6[proc3]),0,1)+IF(ISBLANK(Table6[proc4]),0,1)+IF(ISBLANK(Table6[proc5]),0,1)+IF(ISBLANK(Table6[proc6]),0,1)+IF(ISBLANK(Table6[proc7]),0,1)+IF(ISBLANK(Table6[proc8]),0,1)+IF(ISBLANK(Table6[proc9]),0,1)+IF(ISBLANK(Table6[proc10]),0,1)

 

On the other hand, please use Unpivot Columns and Group by function in Power Query. You can transform selected columns into attribute-value pairs where columns become rows using “Unpivot Columns”. And aggregate row when select group by.

You can refer to below operation to get what you want.

1. I create the following sample data. Right Click->Edit Query, we will get the Edit Query interface in second screenshot.

2. Select the first column (name), on the Ribbon Transform, click on Unpivot Columns > Unpivot Other Columns(highlighted in yellow as follows). From the result, the first column is name, the second is the product type, and the third is the value of each type product, it will elliptic if the value is null.

 

3. Click the Group By, enter the name and select right operation, it will aggregate according to name like below screenshot.

4. Click OK, you will get the expected result. Click close and apply. The table will be changed based on operation. The first picture display the expected result in Edit Query, and the second picture displays the result table in PowerBI desktop.

For more details, please review the article. If you have any question, please feel free to ask.

Best Regards,
Angelia

[flanshaw · Accepted solution]

Great stuff. Works a treat using the first method. thank you.

[BhaveshPatel]

May be THIS could be some help to you