Forum Discussion

Moore55's avatar
Moore55
Regular Visitor
1 year ago
Solved

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...
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

     

Moore55's avatar
Moore55
Regular Visitor
1 year ago

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