Survey Data Responses - transform data

[GrahamC1]

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

[DemoFour · Accepted solution]

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.