Counting days of week in different cells

[Anonymous]

After reading through the community notes, I figured this out. However, wanted to see if there was an more efficient way. Below is an answer to a survey question (what days do you eat burgers?). Data came in Wednesday;Thursday etc. I've split it up in excel. The goal is to have a table that sums up Monday = 1, Tuesday = 2, etc. I anticipate 100's of ID's, so I'm practicing now. I used powerquery to group and then copied pasted to facilitate summarizing. Clunky. PowerBI helped the query, but I could not add the queries up (back to excel). Unclear if all of this can be done in Power BI. First table below are results from 6 people. If I have 350 people...there's got to be a better way to count the days up. Thougths?

ID            Q1.1             Q1.2              Q1.3

14	Wednesday	Thursday
15	Monday	Thursday	Friday
16	Thursday	Friday
17	Tuesday	Thursday
18	Tuesday	Wednesday	Thursday
19	Wednesday	Friday

Q1.2             Count

Wednesday	1
Thursday	3
Friday	2

[parry2k · Accepted solution]

@Anonymous you should unpivot your table

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

from here everything will be super easy to work with.

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!

[Icey · Accepted solution]

Hi @Anonymous ,

I create a simple .pbix file, please check if this meet your requirements:

1. In Power Query editor, UnPivot.

2. Close and Apply.

3. In Power BI Desktop, enter data.

WeekDay table:

4. Sort [WeekDay] column by [Order].

5. Create relationship.

6. Create a measure.

Count = COUNT('Table'[Value])+0

7. Create visuals.

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Awesome. Works perfectly. Thank you. 

[parry2k · Accepted solution]

@Anonymous you should unpivot your table

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

from here everything will be super easy to work with.

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!

[Anonymous]

Spoiler

Thank you. It's the little things. Appreciate the info. Very beneficial.