How to pull real value from a column containing an key value array

[Moore55]

I am working on a report using a database that effectively contains data from a bunch of questions and answers

The problem i am encountering is the value being saved into the answer column is the key not the value but there is a separate column in the same table that contains all the possible options for that question. 

An example would look like: 

In the options column I would have the following text:

{Option1: yes, option2: no, option3: I don't know}

And in the answer column I would have:

Option1

Is there a clever way for me to create a new calculated or custom column that will pull the real value out

Any help is appreciated 

Thank you 

[Moore55]

Hey I discovered that the value column can be a multiple choice as well so in some cases the value will be stored like:

option1,option3,option9

Is there any way to convert theay value column to a list and then loop through the code you provided based off the number of values in a new list and have it output the real value to a concatenated column? So I would want the column to look like this:

Real value 1, real value 3, real value 9

Hope that makes sense and any help is appreciated. 

Thanks

[v-dineshya · Accepted solution]

HI @Moore55 ,

Thank you for reaching out to the Microsoft Community Forum.

Use this M code in the custom column formula:

let
optionsText = Text.Middle([Options], 1, Text.Length([Options]) - 2), // remove outer braces
optionsList = Text.Split(optionsText, ", "),
optionsRecord = Record.FromList(
List.Transform(optionsList, each Text.Trim(Text.AfterDelimiter(_, ":"))),
List.Transform(optionsList, each Text.Trim(Text.BeforeDelimiter(_, ":")))
)
in
Record.FieldOrDefault(optionsRecord, [Answer], null)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[Moore55]

Hey I have just discovered that the value column contains multiple choice answers as well.

So in my value column I will have the text option1,option3

Which causes an error with the code you suggested above as it can't match it to the list.

is there any way to nest a loop in m code? So can I create a list of my values and for every value loop through the other list for a match and output the real value to a concatenated string Column?

So the output would be a new column with test real value 1, test real value 2

Cheers

[Moore55]

Thank you so mach this was a huge help 

[v-dineshya]

Hi @Moore55 ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[bhanu_gautam]

@Moore55 Open Power Query Editor by clicking on "Transform Data" in Power BI.
Select the table that contains your data.
Add a new custom column by clicking on "Add Column" > "Custom Column".
Use the following M code to extract the real value:

let
Options = Text.Middle([options], 1, Text.Length([options]) - 2), // Remove the curly braces
OptionsList = Text.Split(Options, ", "), // Split the options into a list
KeyValuePairs = List.Transform(OptionsList, each Text.Split(_, ": ")), // Split each option into key-value pairs
AnswerKey = [answer], // Get the answer key
RealValue = List.First(List.Select(KeyValuePairs, each _ = AnswerKey)) // Find the value corresponding to the answer key
in
RealValue

Click "OK" to create the custom column.
Close and apply the changes to load the data back into Power BI.