Categorise Likert Survey Responses

[carlG]

Hi PowerBi Community,

Still fairly new to powerBi and hitting a brick wall with queries/relationships.

I need to build a query that takes data from excel responses, (o365 Likert data) and applies a value instead of the text string that is being created. I have tried to use the additional column (pictured) but as the column count will exceed 100+, seems like there is a better way than this manual method.

I also need to put categories on each column, as seen in the sample survey (3D Printers, CNC, etc.)

Survey Sample Link

At a loss, could do easily in Access, trying to see if PowerBi can handle. Any hints would be appreciated

[V-pazhen-msft]

@carlG 

So the survey responses are recorded as Text string? If that's the case, you can work on a copy of the excel. You could replace(Ctrl H) the text strings to numbers in Excel than import into Power BI. Make sure the you choose the correct format when replacing.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carlG]

Thank you for the info,

Was hoping to automate it to eliminate user error. as the survey will be completed monthly.

Was almost thinking a lookup table of sorts if it exists in powerbi

[amitchandak]

@carlG , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[carlG · Accepted solution]

Managed to resolve it in the end

Merge Queries with a new table with the likert String and associated likert value on a separate table.