Quiz score via Flow?

Steel Contributor

It's great that you can use Flow to get Forms info and run workflows, etc, but curiously I see that the score isn't available. Seems like a strange piece to leave off.

14 Replies

I was looking to do the same thing for our HR dept. for Annual review training.  The current solution using infopath is very clunky and could be replaced with Forms Quizes/ Flow if we could determine pass/ fail in the flow.  Total Points is in the excel sheet so why not available in the flow? 


@Clint Lechner wrote:

It's great that you can use Flow to get Forms info and run workflows, etc, but curiously I see that the score isn't available. Seems like a strange piece to leave off.



This has been bugging me for days.

 

I'm trying to use the quiz for Call Quality monitoring, and the score is a key component.

 

Very irritating that there is no clear way to export the score to SharePoint.

I'm looking for the same options with Flow & Forms(Quizzes) but unfortunately is seems there is no solution for the moment.

But maybe voting for this ideea:  Flow Ideas Quiz - Reading "Total Points" Column from Response  will be in the future...

@LucianC , yes, unfortunately no direct solution. As workaround I collect answers by flow in Excel file published on SPO. From another Excel Power Query it, as well as table with answers and points in one more file plus some additional information from HR SQL database. This Excel with it's data model is published on Power BI services for scheduled updates. Using it one more flow sends reminders to people who didn't take the test in time and some summary is published in Teams as Power BI report.

 

Bit complicated, but fully automatic.

@Sergei Baklan, thank you for your message and workaround tip. Until Microsoft will decide to shorten my path I think I will have to use your workaround. :)

 

The first part with getting the answers into an Excel file is done.

But for the second part - using PowerQuery to match the responses with a "CorrectAnswers" table... is harder for me. Could you point me to a link or an example? I'm a "basic user" with PowerQuery and I don't imagine how could I calculate the total points for each answer.

@LucianC , I'll generate small sample, it'll be easy to explain.

@LucianC , let play with such sample

image.png

Quiz table is what the flow updates. Answers is the supporting page with points. Final result is in green.

In my case each of above tables is in separate file - answers are separated for the security reason. Source file is separated to avoid any conflicts with flow since that file is practically never opened in opposite to file with results. Most probably it shall be no conflicts even they are in one file, but who know.

 

First query supporting table and only one transformation is to add the key for Q&A.

let
    Source = Excel.CurrentWorkbook(){[Name="Points"]}[Content],
    ProperType = Table.TransformColumnTypes(Source,{
        {"Question", type text},
        {"Answer", type text},
        {"Points", Int64.Type}}),

    // This is to generate unique key for the combination of Question & Answer
    AddKey = Table.AddColumn(ProperType, "QAKey", each [Question] & "===" & [Answer])
in
    AddKey

Query the quiz table, select all columns but questions, unpivot other columns and add the same key for each combination of Q&A. Merge on this key with Points query (into same or separate table) extracting only points. With that GroupBy on all columns except points, aggregate them by sum.

let
    Source = Excel.CurrentWorkbook(){[Name="Quiz"]}[Content],
    ProperType = Table.TransformColumnTypes(Source,{
        {"Time", type datetime},
        {"Name", type text},
        {"email", type text}, {"Q1", type text},
        {"Q2", type text}, {"Q3", type text}}),

    UnpivotQuestions = Table.UnpivotOtherColumns(ProperType, {"Time", "Name", "email"}, "Attribute", "Value"),
    AddKey = Table.AddColumn(UnpivotQuestions, "QAkey", each [Attribute] & "===" & [Value]),

    MergeWithAnswers = Table.NestedJoin(AddKey,
        {"QAkey"}, Points,
        {"QAKey"}, "Points",
        JoinKind.LeftOuter),
    GetPoints = Table.ExpandTableColumn(MergeWithAnswers, "Points", {"Points"}, {"Points.1"}),
    RemoveUnuzed = Table.SelectColumns(GetPoints,{"Time", "Name", "email", "Points.1"}),

    //Use Advanced option to GroupBy
    SumPoints = Table.Group(RemoveUnuzed,
        {"Time", "Name", "email"},
        {{"Points", each List.Sum([Points.1]), type number}})
in
    SumPoints

That's all, the rest is cosmetic.

 

One more practical point. If you change wording of the question(s) in your form, or even name(s) of the column in Excel table, the flow doesn't care, it will add results correctly based on order of question/columns. Text with answers will be returned by flow as it is. However, both could affect the key as it is generated in this sample. Correction of wording always could happen, even extra space crashes the key.

 

As variant you may play with texts (trim, lowercase, etc), bt that's not very reliable. Better to work with indexes. For the questions they could be generated internally - sequential numbers in supporting table and pick-up  number of columns in quiz query.

 

With answers is more complex, we can't pick-up any ID for them. Thus we use in Forms numbering list of answers, like

Is England part of EU?
  a) Yes
  b) No

with that you extract only first character of each answer, doesn't matter which text will be after that, and work with it.

 

But that's all improvements and more depends on your concrete data.

You may play with attached file to check how quires work.

@Sergei BaklanTHANK YOU SOOOOOO MUCH!!! I'm sorry I could not award you 100 likes :)

Super clear example to follow. I will certainly use it, even if will take some time for me ;)

@LucianC , you are welcome. Idea is simple, but the devil is in the detail, to sort them out is most time consuming part.

@LucianC , I added to the sample renaming questions columns by their IDs since that is required some coding. Steps are

<prev step>
    QuestionIDs = {"1"..Number.ToText(List.Count(Table.ColumnNames(ProperType))-nLastBeforeQuestionsPosition)},
    QuestionsRenameList = List.Zip(
        {List.RemoveFirstN(Table.ColumnNames(ProperType),
        nLastBeforeQuestionsPosition),QuestionIDs}),
    QuestionsToIDs = Table.RenameColumns(ProperType,QuestionsRenameList),
<next step>

IDs for questions also added to Answers table. Please note IDs shall be used as text.

@Sergei Baklan, thank you again for your detailed explanations, and for what I discovered until now from your sample I could mention two important advantages:

  1. Using your approach with an "answers" table I can see that:
    1.  I could award points based on the answer "weight" - this could not be done with Microsoft Forms - Quiz type
    2.  In this case is no longer necessary to use the Quiz Type so I could use a Form Type
  2.  Now because I no longer need a Quiz type form, using a simple Form have another advantages:
    1.  I no longer need Flow to put the answers into an Excel file because for this kind of form an Excel file will be created automatically on the corresponding SharePoint Site
    2.  So PowerQuery will have access directly to online Excel file.

Isn't that great? :)

@LucianC , yes, that's a great idea. Work with surveys linked to Excel is much easier than with quiz. Why we use it that's since user see the points right after submitting the form. They may, even shall, to repeat attempts (not necessary in the same session) till hit the bar collecting required number of points. That's kind of introduction training.

 

In general to show the summary you may use the same connected to the form Excel, put it into another sheet of the same file. I more trust to "co-authoring" with Sharepoint app  which updates the file rather than to the flow.

 

@Sergei Baklan - Thank you, this is so helpful and I was able to set it up! I am new to queries and am going one step further. I have multiple quizzes that will be dumping answers into a single Excel tab with the quiz name on it. Is there any way to direct the quiz answers added by flow into the correct answers table based on the quiz name or do I have to create a separate tab for each quiz flow? I attached a sample of what I'm trying to do.

Unless I missed something looks like YEARS later and still no changes....