Need some guidance with transforming this data in power query

[dpowerbi]

ID	Created	Question1	Question2	…	Question 100	Question 101
1	jan	yes	no		yes	blank/null
2	feb	no	no		blank/null	blank/null

I know unpivoting/pivotig/transposing / turning column headers into first row are part of the solution here, but Im not sure what the right fix is with these tools. 

I would like a visualization ultimately to show the yes/nos but first I need to transform the data (I THINK?).

Because there are many columns and in the future, many more rows will be made for this dataset, Im not sure if transposing is the right way to do this as it will duplicate the questions for each ID, right? so id 1 and id 2 will be duplicated with 100 questions each (200 questions)

what id like is just a way to group by created date and to show the number of "nos" or the number of "yes's"" in total for each month.

id rather not do a long if statement to count the no's /yes's for each ID, but that is an option, but im posting this to see how others would take this data to visualize it?

the ask for the data is to show the number of yes/nos for each ID, but also (and I think the only way to show this is a matrix table) a visualization of the number of yes/nos by each particular question asked..

"

[v-jingzhang · Accepted solution]

Hi @dpowerbi 

Since you have many questions and the questions may increase in the future, it is better to unpivot the question columns. You can select [ID] and [Created] columns at the same time, then select "Unpivot other columns" option. This works well when the question numbers increase in the future. 

Then apply the change to Power BI Desktop. In Power BI Desktop, you can create measures to count the number of Yes or No. Then use Table or Matrix visuals to display the count results. You may download my sample file attached at bottom. It includes several measures and several visuals displaying the results. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.