Forum Discussion
Survey Data Responses - transform data
Hi,
I have a survey that was created in Microsoft forms, the exported data in excel shows the questions across the top (columns) and answers in the rows.
Example: one questions is 'I am happy with where I work' and the responses are Strongly Agree, Agree, Neutral, Disagree, Strongly Disagree
I can create a visual showing all 5 responses but I now need to show them as 3 responses - Favourable, Neutral and Unfavourable
How can I calculate the ones that have picked strongly agree and agree as one total value, and disagree, strongly disagree as another value
I can do this in excel with the COUNTIFS function but I cant work this out in PowerBI, do I need to create a new column for each question with these new values in? If so, how do I do this?
I still need to link to the original data so we can filter the visuals by team, office, gender etc
Hi GrahamC1
You need to set up some buckets for the outputs either in the Query editor with a conditional column inserted into your data structure or in desktop using the UI functionality Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn this way you can map the replies to the Outputs of Favorable, neutral etc.
In regards to your wider custion about countifs you can use a number of DAX functions, but if you have not built a Star Schema data model, and you are using your flat excel file, then this is why you arr running into issues. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Hope this helps, I ahve built lots of surveys like this in Power BI so please feel free to ask for a more specfic answer.
The choice is yours to which way you want to do this.
1 Reply
Hi GrahamC1
You need to set up some buckets for the outputs either in the Query editor with a conditional column inserted into your data structure or in desktop using the UI functionality Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn this way you can map the replies to the Outputs of Favorable, neutral etc.
In regards to your wider custion about countifs you can use a number of DAX functions, but if you have not built a Star Schema data model, and you are using your flat excel file, then this is why you arr running into issues. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Hope this helps, I ahve built lots of surveys like this in Power BI so please feel free to ask for a more specfic answer.
The choice is yours to which way you want to do this.