topic Re: How to pull real value from a column containing an key value array in DAX Commands and Tips

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

Moore55 — Mon, 21 Apr 2025 19:22:18 GMT

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

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

bhanu_gautam — Tue, 22 Apr 2025 06:15:46 GMT

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.

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

v-dineshya — Tue, 22 Apr 2025 12:15:23 GMT

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

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

v-dineshya — Mon, 28 Apr 2025 10:13:05 GMT

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

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

Moore55 — Mon, 28 Apr 2025 10:30:25 GMT

Thank you so mach this was a huge help

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

Moore55 — Tue, 29 Apr 2025 10:01:36 GMT

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

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

Moore55 — Tue, 29 Apr 2025 10:20:15 GMT

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