Forum Discussion
Creating logic for a Correct or Incorrect Flag in table
Hi jakaihammuda ,
Since you can be sure that all questions are objective, we can use a very simple way to complete this question. I recreated the test data set:I assume that the questions are single choice, unordered multiple choice, judgment, sorting on two items and sorting on three items.
Then as before, New Source -> 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", {"CorrectAnswers", "IsOrder"}, {"CorrectAnswers", "IsOrder"}), #"Added Custom" = Table.AddColumn(#"Expanded DIM", "Custom", each if [IsOrder] = 0 then if [Answer] = [CorrectAnswers] then "Right" else if Text.Contains([CorrectAnswers], [Answer]) and not Text.Contains([Answer], [CorrectAnswers]) then "Lack of other answer" else "Wrong" else if [Answer] = [CorrectAnswers] then "Right" else if Text.Length([Answer]) = Text.Length([CorrectAnswers]) then "Wrong order" else "Lack of other answer"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IsOrder", "CorrectAnswers"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"UserID", "Answer", "Custom", "QuestionID"}) in #"Reordered Columns"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.
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.
Hi, Anonymous
Thank you so much for helping on this!
I think im almost there now but just the one issue regarding the "keywords", this isnt optimal for me to use based on the fact that some of the keywords may appear in some other question answers that are wrong. Its best if it soley do it on the correct answer as opposed to keywords.
This is my structure.
FACT:
Contains the user, results ID, time, Answers and Question ID
DIM table (Correct Answer):
DIM table (Question)
I still want that logic or Correct, partially correct, Incorrect that youve created but i just cant base it off keywords 😞 the partially correct logic can still apply right? using the "&" adn checking on words before/after that to see if its a match with any in the actual correct answer?
Is much amending needed to be done to just base it off the actual corrcet asnwer only?
Hi jakaihammuda ,
I used keywords because I wasn't really sure what type of question your topic was.
If you have a lot of subjective questions, i.e. you need the user to organize his/her own answers, then there may be cases where the user's response is not exactly the same as your standard answer, but it is still correct, and then if you use the standard answer exclusively, then this type of response will be flagged as an error.
However, if all your questions are objective, such as (multiple) choice or judgment questions, i.e., you can guarantee that the answer provided by the user is word-for-word the same as your standard answer, then it doesn't matter. But if all the questions are objective, then maybe the method I provided before will be a bit tricky, because I use field matching, and there should be a simpler way to do it.
So I still have to ask you to confirm that you can guarantee that your users' answers can be made to look exactly like your standard answers or not.
Best Regards,
Dino TaoHey Anonymous ,
So yes. All of the questions that are being worked with are objective. The users have to select the choices (max 2 in some cases) or just one result. There is no free text out of these answers.
However there is just the one question where they have to re order tiles to display the correct order or process steps, so technically there is only one correct answer, so would be the equivalent as a one choice selection being correct or incorrect.
The only issue i found was that, specifically with questions that have a "select 2". the users can select the correct two answers, but the order in whihc they select them first depends on the display order in the data/sharepoint file where it gets saved. as ou can see in the screenshot, its not a problem because i have 2 correct aswer columns (1 and 2) to cater for either or of the combinations (as there can only be max 2 combinations). however its not ideal i did that, i only did so becaause it kept it easier to work with. For example if we added a question where they had to select 3 asnwers to be correct, the options of order they can select them in increases, thus meaning id have to create even more correct answer columns to cater for all possibilties. woudlnt be ideal, but i only did that because i dont think the questions will be changing.
does this help make it more clear for you? 🙂
thank youHi jakaihammuda ,
Since you can be sure that all questions are objective, we can use a very simple way to complete this question. I recreated the test data set:I assume that the questions are single choice, unordered multiple choice, judgment, sorting on two items and sorting on three items.
Then as before, New Source -> 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", {"CorrectAnswers", "IsOrder"}, {"CorrectAnswers", "IsOrder"}), #"Added Custom" = Table.AddColumn(#"Expanded DIM", "Custom", each if [IsOrder] = 0 then if [Answer] = [CorrectAnswers] then "Right" else if Text.Contains([CorrectAnswers], [Answer]) and not Text.Contains([Answer], [CorrectAnswers]) then "Lack of other answer" else "Wrong" else if [Answer] = [CorrectAnswers] then "Right" else if Text.Length([Answer]) = Text.Length([CorrectAnswers]) then "Wrong order" else "Lack of other answer"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IsOrder", "CorrectAnswers"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"UserID", "Answer", "Custom", "QuestionID"}) in #"Reordered Columns"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.