Count instances of values in multiple dynamic columns

[pawel_osipowski]

Hi,

My data model will change number of following columns based on a source:

  c1       c2       c3       c…
Mr a    Mr b   Mr c   Mr z
Mr c     null    null     null
Mr b    Mr d   null     null

There are also other columns but not related to the analysis.

How to create a table showing occurences of all values within dynamic number of c columns:

Mr b  2
Mr a  1

Mr c  1

Mr d  1

Mr z  1

etc. 

If that helps, c1 is never empty.

Thanks!

[nvprasad]

HI,

Please do pivot transformation to get all the values into 1 column then create a simple count measure to get the count.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[parry2k · Accepted solution]

@pawel_osipowski you should unpivot your table

- transform data

- add index column in your table
- select index column 
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

Now you can visualize your data in any visual and get the count.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡