Forum Discussion
baobab
7 years agoRegular Visitor
Rankings from Microsoft Forms
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 hypothetica...
Anonymous
7 years agoNot applicable
Yes, it is possible to almost replicate this in Power BI. It will take the following steps:
- Go to "Edit Queries".
- 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.)
- 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.
- 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.)
- 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).
- 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.
- 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).
- 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.)
- Rename the columns, from left to right as 1, 2, 3...9, 10.
- Unpivot the columns.
- 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".
- 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.
- Now, choose the "Stacked bar chart" visualization and fill in as follow:
- Axis: From your new query table, select "Index", followed by "Category", in that order.
- Legend: From your new query table, select "Rank".
- Value: From your new query table, select "Rank", but switch the display option to "Count of Rank"
- 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.
- ARumrill7 years agoAdvocate I
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! - Snazzybecc3 years agoFrequent Visitor