Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
you can try this
in PQ, select the first column and unpivot other columns
then you can use matrix visual to get the expected output
Proud to be a Super User!
Sample Data looks like this:
I couldn't figure out how to attach a file to this thread.
record_id | 1A | 1B | 2A | 2B |
3 | 1 | 1 | 2 | 2 |
4 | 3 | 3 | 3 | 2 |
5 | 2 | 2 | 3 | 3 |
5 | 2 | 2 | 3 | 3 |
6 | 1 | 1 | 2 | 2 |
10 | 1 | 2 | 3 | 3 |
11 | 3 | 3 | 2 | 3 |
15 | 3 | 3 | 2 | 2 |
18 | 1 | 2 | 2 | 3 |
22 | 1 | 1 | 1 | 2 |
23 | 2 | 3 | 3 | 3 |
24 | 2 | 2 | 2 | 2 |
26 | 3 | 2 | 2 | 2 |
27 | 3 | 3 | 3 | 3 |
28 | 1 | 2 | 3 | 2 |
31 | 3 | 3 | 3 | 3 |
you can try this
in PQ, select the first column and unpivot other columns
then you can use matrix visual to get the expected output
Proud to be a Super User!
Thank you!!
you are welcome
Proud to be a 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?
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
71 | |
38 | |
29 | |
26 |
User | Count |
---|---|
97 | |
96 | |
60 | |
44 | |
41 |