Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I've created a Form with 3 questions (Course 1, Course 2, Course 3) and the answers are the days of the week, single select, in a drop-down menu.
So the form structure is like so:
Course 1
.Monday
.Tuesday
.Wednesday
.Thursday
.Friday
.Saturday
.Sunday
Course 2
.Monday
.Tuesday... you get the idea.
Now this Form is connected to an online Excel file in which each question naturally is a column.
Response ID | Course 1 | Course 2 | Course 3 |
1 | Tuesday | Thursday | Monday |
2 | Monday | Wednesday | Friday |
3 | Friday | Wednesday | Sunday |
4 | Tuesday | Thursday | Monday |
5 | Monday | Wednesday | Friday |
6 | Friday | Wednesday | Sunday |
7 | Monday | Monday | Tuesday |
8 | Saturday | Saturday | Friday |
Here's my issue. How do I create a Pie Chart (of any chart) that shows me how many times each day has been selected, regardless of the question (Course 1, 2 or 3).
I guess I need some sort of grouping? I just want a graph that forgets about the question, and only cares about how many times that answer was chosen.
Thank you!
Solved! Go to Solution.
Hello
To get that data in a pie chart format, you can do the following in Power Query Editor:
1. Highlight course 1, course 2, and course 3 columns and 'despivos columns' (button in the transformation menu)
2. Add a new column called something like 'count' and just add the value 1 to each row (make sure the column type is set as an integer), you should get a table like this:
3. Create a pie chart with the 'count' column as the value and the day of the week column as the legend.
I have a chart like this:
I hope that's what you were looking for.
The
Thank you @BI_Jo and @amitchandak very helpful and I now understand how to display data the way I was looking for!
Now, what happens when data keeps coming? This excel table is connected to an online form and gets data constantly... Is there a way to automate the pivoting without messing up the responses in the online excel?
Hi
As you refresh the project then the data from the excel files will be updated. When you transform the data in Power Query Editor these steps will be repeated everytime you refresh, so the pivot will automatically be part of the refresh.
As a general rule I would try and unpivot data when you bring it into Power BI.
@fpinero , Please find the attached file after signature. Unpivoted the data
https://radacad.com/pivot-and-unpivot-with-power-bi
Video -https://www.youtube.com/watch?v=2HjkBtxSM0g
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hello
To get that data in a pie chart format, you can do the following in Power Query Editor:
1. Highlight course 1, course 2, and course 3 columns and 'despivos columns' (button in the transformation menu)
2. Add a new column called something like 'count' and just add the value 1 to each row (make sure the column type is set as an integer), you should get a table like this:
3. Create a pie chart with the 'count' column as the value and the day of the week column as the legend.
I have a chart like this:
I hope that's what you were looking for.
The
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |