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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
msims4
Frequent Visitor

Trying to create one table with multiple columns

msims4_0-1734563703890.png

I am new to Power Bi. I have survey results, where the questions are 1A, 1B, and I would like to create a table where the rows are Response "1",  "2", "3" and the Columns are 1A, 1B. There should be three rows and not 8 combinations of 1 & 1, 1& 2, etc.  
1A should have 25, 42, 35 and 1 B should have 14, 49, 39. 

This formula in power query works for one column: = Table.Group(#"Removed Columns1", {"1A"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}) 

Once I add a second column, it creates additional combinations. 
I hope I'm explaining well enough, I've been starring at this for a while. 

Thanks! 

1 ACCEPTED SOLUTION

@msims4 

you can try this

 

in PQ, select the first column and unpivot other columns

 

11.png12.PNG

 

 

then you can use matrix visual to get the expected output

 

13.PNG





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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
msims4
Frequent Visitor

Sample Data looks like this: 

I couldn't figure out how to attach a file to this thread. 

 

record_id1A1B2A2B
31122
43332
52233
52233
61122
101233
113323
153322
181223
221112
232333
242222
263222
273333
281232
313333

@msims4 

you can try this

 

in PQ, select the first column and unpivot other columns

 

11.png12.PNG

 

 

then you can use matrix visual to get the expected output

 

13.PNG





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

Proud to be a Super User!




Thank you!!

you are welcome





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

Proud to be a Super User!




ryan_mayu
Super User
Super User

You can create a dim table which is 1, 2, 3 and connect this table to 1A and 1B.

 

what dose your sample data look like? could you pls provide some sample data?





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

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors