Forum Discussion
How to pull real value from a column containing an key value array
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
Super UserMoore55 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.