cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahmoud Frequent Visitor
Frequent Visitor

Create Matrix From Five Columns

I have five questions, each question has four awnsers options(as appear in the picture down). In Excel, I used countif function to create the table and get the values. I would to create same table in Power BI?

Is that posible? After that create a chart! 🙂

 

Questions.jpg

Thanks for your support in advans!

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

Assiming your data is in the format you present In the query view you need to select all the questions columns and unpivoted them you will get a table with 3 columns:
Answers type
Attribute - questions
Count

Then in the matrix visual put the columns in the index below:
Answers type - Rows
Attribute - columns
Count - values

This should give what you.want.

Regards,
MFelix




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

 

I was looking at your data and I would do a simple treatment of the information. I would take all the reply and ID column into one table and unpivoted them so would have a table with 3 columns: ID, Question, answer them would do a table with the rest of the question (age, start end date, ...) also with ID and keep it as is and then do the relationship between both tables by ID this would give you the reply that you need for your visuals but also a base with additional information to make the age of respondent visuals, etc...

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

 

This is one thing I noticed in your data, in your replies you have columns that are text and others that are numbers, problably you should divide between two tables one with the text values and another with the number values that way you could do the statistic analysis in you data and the quality also.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

Assiming your data is in the format you present In the query view you need to select all the questions columns and unpivoted them you will get a table with 3 columns:
Answers type
Attribute - questions
Count

Then in the matrix visual put the columns in the index below:
Answers type - Rows
Attribute - columns
Count - values

This should give what you.want.

Regards,
MFelix




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

mahmoud Frequent Visitor
Frequent Visitor

Re: Create Matrix From Five Columns

Hi @MFelix

Sorry to back to you late.

I have a question, after unpivot the columns, it will create many rows and that will effect on the other columns counted values ( the number of rows equal to number of new rows from unpivoted columns).

 

So if I have 30 culomns, and I would to unpivot some culomns and count the other ( I would to unpivot columns from 5 to 10, then the columns 11 to 14, then 16 to 20, and then 23 to 27). At the same time I have to count the values in column 1 and have charts from other columns(2,3,4,6,15,22,...etc).

 

As I tried for first time, the numbers of rows were equal to the number of rows in unpivoted columns, but that effected in the correct number.

 

I hope I am clear and get best way to fix that.

Thanks for you support!

Mahmoud

 

Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

 

You are correct when you say that there will be an increase on the other rows, because for each row you have it will multiplied bty the number of columns you unpivot.

 

I don't know how your data is set up but depending on the result you want/need you probably will have to do some further query changes or change the way your charts are calculated.

 

Without further information I cannot help you in the right way.

 

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mahmoud Frequent Visitor
Frequent Visitor

Re: Create Matrix From Five Columns

Hi@MFelix thanks for your quick response.

 

Kindly find on this LINK an Excel file which is an example of the data I am working on.

I have many scenarios in my mind but I do not know which one is the best(fast and less storage)

(I have 30 questions multiple options which means 244 columns)

1- Create an Excel file for each questions (30 files) with the ID column then create relations between the tables. 

2- Copy and Past the orginal table 30 times then remove the other columns then create relations between the tables.

After that in both cases unpivot the columns.

Which one is faster and need less storage? if that OK :). 

 

If you have better solution, please let me know.

Thanks for your support!

Mahmoud

 

Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

 

I was looking at your data and I would do a simple treatment of the information. I would take all the reply and ID column into one table and unpivoted them so would have a table with 3 columns: ID, Question, answer them would do a table with the rest of the question (age, start end date, ...) also with ID and keep it as is and then do the relationship between both tables by ID this would give you the reply that you need for your visuals but also a base with additional information to make the age of respondent visuals, etc...

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

mahmoud Frequent Visitor
Frequent Visitor

Re: Create Matrix From Five Columns

Hi @MFelix 

Thank you this good, I created the table you mentioned, then tried to create a matrix. Create a filter based on the attribute column but the value column I could not create a filter and it give the total rows numbers instead of the values I looking for.

 

Any suggestion to fix that?

Uncorrect values.png

Super User
Super User

Re: Create Matrix From Five Columns

Hi @mahmoud,

 

This is one thing I noticed in your data, in your replies you have columns that are text and others that are numbers, problably you should divide between two tables one with the text values and another with the number values that way you could do the statistic analysis in you data and the quality also.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,418)