Forum Discussion
How to pull real value from a column containing an key value array
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
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
6 Replies
- bhanu_gautam
Super User
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
RealValueClick "OK" to create the custom column.
Close and apply the changes to load the data back into Power BI. 
v-dineshyaCommunity Support
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- 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 Thank you so mach this was a huge help
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
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
