Jun 20 2017
05:32 PM
- last edited on
Aug 06 2023
01:54 PM
by
TechCommunityAP
Jun 20 2017
05:32 PM
- last edited on
Aug 06 2023
01:54 PM
by
TechCommunityAP
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.
Aug 04 2017 08:47 AM
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?
Nov 17 2017 06:56 AM
@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.
May 14 2019 01:16 AM
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...
May 15 2019 03:15 AM
@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.
May 15 2019 05:51 AM
@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.
May 15 2019 06:09 AM
@LucianC , I'll generate small sample, it'll be easy to explain.
May 15 2019 07:22 AM
@LucianC , let play with such sample
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.
May 15 2019 07:31 AM
@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 ;)
May 15 2019 07:44 AM
@LucianC , you are welcome. Idea is simple, but the devil is in the detail, to sort them out is most time consuming part.
May 15 2019 09:59 AM
@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.
May 15 2019 10:45 AM
@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:
Isn't that great? :)
May 15 2019 11:22 AM
@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.
Jul 10 2020 08:50 AM
@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.
Nov 14 2022 05:51 PM
Unless I missed something looks like YEARS later and still no changes....