Creating a total score for values in a concatenated and comma separated list

[Sunbeam21]

My data source is in Excel and it contains responses to a list of questions, many of which involve selecting multiple values from a list of values.  In the Excel output, these multiple responses are recorded in a single cell as a concatenated, comma separated list.  Each value has a weighted score and I need to calculate the total score for all the selected responses, ideally by looking up the score from a table.  See example below:

Desired result = Total score column shown in first table.  The lookup table for the scores for each 'fruit value' is set out below that.  Is there any way to do this in a fairly easy way?

Fruit	Total score
Apple, Lemon, Pear, Orange	21
Lemon, Pear	6
Apple	10
Orange	5

Lookup table	Score
Apple	10
Lemon	4
Pear	2
Orange	5

[ImkeF · Accepted solution]

Yes: In your first table ("Combinations"), you add a column that splits the text-string with the combinations into a list:

Text.Split([Combinations], ",")

Then you expand that column. This will produce a row per fruit.

Next step is to clean that text befor merging it with the LookupTable.

You then expand the lookup value.

Last step you group on the combination-field and aggregate those values with a sum.

This is the full code:

let
    Source = Combinations,
    #"Added Custom" = Table.AddColumn(Source, "List", each Text.Split([Combinations], ",")),
    #"Expanded List" = Table.ExpandListColumn(#"Added Custom", "List"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded List",{{"List", Text.Trim}}),
    #"Merged Queries" = Table.NestedJoin(#"Trimmed Text",{"List"},LookupTable,{"Fruit"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Score"}, {"Score"}),
    #"Grouped Rows" = Table.Group(#"Expanded NewColumn", {"Combinations"}, {{"Total Score", each List.Sum([Score]), type number}})
in
    #"Grouped Rows"