Require help with essentially using a Find/Replace statement

[Alan-Redwah]

Hello there,

Unfortunately I'm a newbie when it comes to PowerBI and data management, have been trying to find this out through the community and Google but just haven't quite found an article that makes sense to me. 

In my head there should be an easy basic solution to this, I just can't quite figure it out. 

Context of the query, we have surveyed a number of people and they returned answers to questions, these answers are numeric in our results excel workbook, each number is equal to a text value in another excel workbook within seperate sheets (the code book). 

Here is an example of our results sheet column (left image, numbers in filter dropdown) compared to the code book text in my other workbook sheet (right):

Throughout the results sheet there are 30 of these columns, all referencing the codebook which are stored in different sheets within the excel code book with their text values.

I would like to create one Master PowerBI table that would have all the numeric values converted into our text for each of the users surveyed. 

I know that I could do a Find/Replace but the values will all change next year so I'd like some code to be able to automatically run through the sheets to change the text values in my results so I can represent it in PowerBI.

Hopefully that makes sense.

Please ask any questions if it is not clear.

If this has been covered please let me know and I'll go read the article as I don't want to take up peoples time.

Kind regards,

Alan 

[lbendlin]

Throughout the results sheet there are 30 of these columns

The very first thing you will want to do is to unpivot that data to bring it into a format that is usable by Power BI.

What exactly are you trying to find/replace?