Creating logic for a Correct or Incorrect Flag in table

Hi jakaihammuda ,

Back for work!

First of all on the issue of separators, whether you choose to use "," or ";" or "&" are not relevant, you just need to modify the code in this place:

Note, however, that as far as possible, only one separator is used in this column, and it may be troublesome to use multiple separators, such as both commas and semicolons.
Also based on this screenshot of yours, I see that you have spaces between the & and the character, so I'm afraid you need to count the two spaces before and after the & as separators as well.

Then regarding the order issue, my suggestion is that you can add another new column Isorder in the DIM table and mark it as 1 if there is an order issue with the answers to the current question, otherwise mark it as null. as shown below:

In the FACT table I added some sample data:

Then create a new Blank Query and put all of the M function into the Advanced Editor:

let
    Source = Table.NestedJoin(FACT, {"QuestionID"}, DIM, {"QuestionID"}, "DIM", JoinKind.FullOuter),
    #"Expanded DIM" = Table.ExpandTableColumn(Source, "DIM", {"keyword", "Isorder"}, {"keyword", "Isorder"}),
    CustomCheck = Table.AddColumn(#"Expanded DIM", "Answer Check", each 
        let
            AnswerLower = Text.Lower([Answer]),
            lowerKeyword = Text.Lower([keyword]),
            KeywordsList = Text.Split(lowerKeyword, ","),
            CheckList = List.Transform(KeywordsList, each Text.Contains(AnswerLower, Text.Trim(Text.Lower(_)))),
            AllKeywords = List.AllTrue(CheckList),
            AnyKeywords = List.AnyTrue(CheckList)
        in
            if [Isorder] = 1 then 
                let
                    CheckAnswer = (QuestionID as number, keyword as text, Answer as text) => 
                    let
                        keywordList = Text.Split(lowerKeyword, ","),
                        checkOrder = List.Accumulate(
                        keywordList, 
                        [FoundAll = true, LastIndex = 0, WrongOrder = false], 
                        (state, currentKeyword) =>
                            let
                                currentIndex = Text.PositionOf(AnswerLower, currentKeyword, Occurrence.First),
                                isCurrentFound = currentIndex <> -1,
                                isNewIndexBigger = currentIndex > state[LastIndex],
                                isWrongOrder = state[WrongOrder] or (isCurrentFound and not isNewIndexBigger),
                                hasFoundAllSoFar = state[FoundAll] and isCurrentFound
                            in
                                [
                                    FoundAll = hasFoundAllSoFar,
                                    LastIndex = if isCurrentFound then currentIndex else state[LastIndex],
                                    WrongOrder = isWrongOrder
                                ]
                    ),
 
                    result = if not checkOrder[FoundAll] then 
                                "Lack of other answers" 
                            else if checkOrder[WrongOrder] then 
                                "wrong order" 
                            else 
                                "right"
                in
                    result
            in
                CheckAnswer([QuestionID], [keyword], [Answer])
            else if AllKeywords then 
                "Right" 
            else if AnyKeywords then 
                "Lack of other answers" 
            else 
                "Wrong"),
    FinalTable = Table.RemoveColumns(CustomCheck,{"keyword"})
in
    FinalTable

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.