Rankings from Microsoft Forms

[baobab]

Hi, 

I made a survey using Microsoft Forms and many of my questions were rankings. However, the results are put into a single cell and are delimited by semicolumns. For instance, in a hypothetical "Question 1" if I ranked first Option B and then Option G and finally Option A, I would get a single cell under the column "Question 1" which said "Option B;Option G;Option A". 

Can I translate these results into a chart like this one below that you see in Forms? Or at least in any other chart? 

Thank you very much!

[JorgeBlanco]

Hi.

You just need to create a custom colum with a table inside and a rating to each of the elections, then deploy the table and calculate the Sum of ratings vs the Options

I created as example a table with the options selected separated by semicolons (ordered from more to less importance):

You need now to create a Custom Column with the following code

let
  optionsList = Text.Split([Option],";"),
  optionsTable= Table.FromList(optionsList),
  AddRating = Table.AddIndexColumn(optionsTable,"Rating", 3,-1)
in
  AddRating

You get a column with Tables

Simply Expand the tables

And you can the plot the new Column1 vs the sum of Rating (make sure Rating data type is a number)

Hope this resolves your question. Case it is, please mark it as a solution.

[anthrakia]

Yes, it is possible to almost replicate this in Power BI.  It will take the following steps:

1. Go to "Edit Queries".
2. Select the Table icon at the top-left corner of your data and click on "Add Index Column".  (You can choose "From 0" or "From 1" - just remember your choice and be consistent.)
3. Go to your ranking question column, right-click and select "Add as New Query".  You will have a new table created that only contains your ranking question data.  Navigate to that new table and notice that your data is in the form of a list.
4. Right-click on the "List" column and select "To Table".  (You can also select "To Table" from the "Transform" tab of the top navigation ribbon.)
5. Right-click on "Column1" and select "Split Column", "By Delimiter" and choose semicolon as the delimiter.  For "How to handle extra columns", choose "Accumulate into the last column" (if you want to review any "extra" data) or "Truncate extra columns" (if you know you shouldn't be expecting any "extra" columns).
6. Select the Table icon at the top-left corner of your data and click on "Add Index Column".  Choose "From 0" or "From 1" - just make sure you select the same option as from Step 2 above.
7. If you chose "Accumulate into the last column" in Step 5, review the right-most column now.  You may need to extract values by repeating Step 4 or Step 5 above on this right-most column.  After extracting, and confirming that you do not need the data, you may remove the right-most column(s).
8. Filter any blank rows out.  (You may have blanks if you implemented branching on MS Forms, or if the question was optional: i.e. not all respondents may have an answer.  Don't worry about losing track of the correct respondent - your indexing will take care of that.)
9. Rename the columns, from left to right as 1, 2, 3...9, 10.
10. Unpivot the columns.
11. You can now rename the "Attribute" column as "Rank" and the "Value" column to whatever makes sense as a category name for your options as described.  For the purposes of these instructions, we'll call it "Category".
12. Go to Relationship view and make sure that "Index" in your form data (most likely named "Form1" if you kept to all the defaults) is linked to "Index" in your new query table containing the data from your ranking question. 
13. Now, choose the "Stacked bar chart" visualization and fill in as follow:
    1. Axis: From your new query table, select "Index", followed by "Category", in that order.
    2. Legend: From your new query table, select "Rank".
    3. Value: From your new query table, select "Rank", but switch the display option to "Count of Rank"
14. At the top-left corner of your Stacked bar chart visualization, you should see 3 icons with arrows.  There is an arrow pointing up on the left, 2 arrows pointing down in the middle and a branched double arrow on the right.  Click on the middle icon.

You should have the visualization that you are looking for at this point.  A couple of limitations:

• You can also set the Tooltips in the visualization to "Count of rank" so that when you mouseover you can see the count of the ranked item vs the total responses.  What I have not figured out is how to represent all of this in percentages as you would see in the default report generated by MS Forms.
• If you have 10 items ranked - the order may not be as you had expected.  i.e. it would probably be in alphabetical order of the rank, i.e. 1, 10, 2, 3, 4... etc.  To get around this you will need to create another table, "Rank_Order" with two columns, "Rank" and "Order" (a simple index column from 1:10) and link "Rank" between this table and the query table you created in Steps 1-11 above.  You can then Sort the "Rank" column by "Order", to force the numerical order.

[Snazzybecc]

thanks  @anthrakia

this worked a treat, very much appreciated

Sam

[ARumrill]

---

You should have the visualization that you are looking for at this point.  A couple of limitations:

• You can also set the Tooltips in the visualization to "Count of rank" so that when you mouseover you can see the count of the ranked item vs the total responses.  What I have not figured out is how to represent all of this in percentages as you would see in the default report generated by MS Forms.

---

Figured how to get the percentages for this visualization to work properly. Within the querying process, for the rank-order new query, add a column that does a DISTINCTCOUNT of the Index column. Then within the report you need to create three measures:

Nominator Value = COUNTX('Rank-Order Data',[Value])

Denominator Value = AVERAGE('Rank-Order Data'[Distinct Index])

The last measure will simply divide these two:

% Vote = [Nominator Value]/[Denominator Value]

When you put this into the visualization's Tooltips it'll start looking more like the one in MS Forms!

[v-piga-msft]

Hi @baobab，

If I understand your scenario correctly that you have got data from Microsoft Forms in Power BI Desktop and want to create the visual silmilar with the chart you upload with the Ranking of the questions?

I'm not familar with Microsoft Forms, if it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

In addition, for your requirement, the conditional formatting may be your choice.

Best Regards,

Cherry

[baobab]

Hi v-piga-msft, thanks for replying.

You understood the scenario correctly. If not the same visual, I'd like at least to plot that data in some kind of chart. Here's the dummy file. I've kept two columns, one with the time to identify each responder and another one with the actual data. 

Basically, the ranking from MS Forms is translated into excel in a single cell, with every choiche of the ranking ordered from first to last choice, divided by a semicolumn.  

Thank you!