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
Community SupportHI 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
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