Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fpinero
Frequent Visitor

How to show in a chart data from 3 different columns (questions) but having same values (answers)

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 IDCourse 1Course 2

Course 3

1TuesdayThursdayMonday
2MondayWednesdayFriday
3FridayWednesdaySunday
4TuesdayThursdayMonday
5MondayWednesdayFriday
6FridayWednesdaySunday
7MondayMondayTuesday
8SaturdaySaturdayFriday

 

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!

1 ACCEPTED SOLUTION
BI_Jo
Resolver III
Resolver III

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:

BI_Jo_1-1604621295792.png

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:

BI_Jo_0-1604621190945.png

I hope that's what you were looking for.

The

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @fpinero ,

Could you tell me if your problem has been solved based on @BI_Jo 's solution? If it is, kindly Accept it as the solution. More people will benefit from it. 

 

Best Regards,
Eyelyn Qin

fpinero
Frequent Visitor

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.

amitchandak
Super User
Super User

BI_Jo
Resolver III
Resolver III

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:

BI_Jo_1-1604621295792.png

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:

BI_Jo_0-1604621190945.png

I hope that's what you were looking for.

The

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.