Best way to deal with unpivoting data

[travbum]

I'm using this face data source to learn about unpivoting data:

UserID	Question2	Question3_1	Question3_2	Question1_1	Question1_2	Question1_3
0	TRUE	0	9	Agree a lot	Agree	Neutral
1	FALSE	5	9	Disagree	Agree	Agree
2	TRUE	67	8	Agree a lot	Disagree a lot
3	FALSE	3	2	Disagree	Agree a lot	Agree a lot
4	TRUE	9	3		Disagree	Disagree
5	TRUE	9	5	Agree	Disagree	Disagree
6	TRUE	0	7	Agree a lot		Neutral
7	FALSE	5	67	Agree a lot	Agree	Neutral
8	FALSE	67	3	Disagree	Agree
9		3	9		Disagree a lot	Disagree
10	FALSE	9	9	Agree a lot	Agree a lot	Agree a lot
11		9	5	Disagree	Disagree	Disagree
12	TRUE	8	67	Disagree		Disagree
13		2	3	Agree	Disagree a lot	Agree
14	TRUE	3	9	Disagree	Disagree	Disagree
15		5	9	Agree a lot	Agree a lot	Agree a lot
16	TRUE	7	8		Disagree
17	FALSE	8	34	Agree a lot	Agree a lot	Agree a lot

First issue I realized is that unpivoting removes any null cells so I realize I have to change from null to some other value to stop this from happening. 

Second issue, as stuff is unpivoted, there are many near duplicates such as in the first three rows of the Power BI Screenshot above. What is the best way to deal with this? 

Anything else I'm missing or should be aware of? 

[v-haibl-msft]

@travbum

The first one should just be an expected action for unpivoting with Power Query. If we want to keep the null cells, we can replace them with some other values do not exist and change back to null after unpivot.

For the second one, why do you think it is an issue? They are different rows for different questions and answers. What is your expected output here?

Best Regards,

Herbert

[travbum]

@v-haibl-msft - As far as the second question, if I wanted to do a sum of the values in column "Question 3 Answer", I would get 37 (0 + 0 + 0 + 9 + 9 + 9 + 5 + 5) instead of the real value of 14 (0 + 9 + 5). 

[v-haibl-msft · Accepted solution]

@travbum

With this unpivoting table, you can create another summarize table with following DAX formula.

Table2 = 
    SUMMARIZECOLUMNS ( Table1[UserID], Table1[Question3], Table1[Question3 Answer] )

Best Regards,

Herbert