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