Forum Discussion
Trying to create one table with multiple columns
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!
5 Replies
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?
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
