Unpivoting removes rows with null values

Power Query
1

Hi, I had been thrown off by this for few hours, when I realized what was happening
So below you would notice that the Feedback# had 2746 distinct values - some of these feedback numbers had all null values for different feedback questions.

Annotation 2021-10-27 011647
Annotation 2021-10-27 0116471159×575 120 KB

Once I Unpivoted(all columns other than feedback#) , the Feedback # showed 1863 values. Now I did check, the difference of 883 is basically the those Feedback# that had all null values for different feedback questions.

Annotation 2021-10-27 011816
Annotation 2021-10-27 011816513×510 56.8 KB

I had not expected this behaviour of power query. I just assumed that just as previously it was displaying a feedback#, that had all null values, it would continue to do the same even as I unpivoted the other columns.

Please can you share some more information on this , or any resource that I can refer to under stand this behaviour of power query

Thanks,

2

This is expected behavior, unpivoting is a great way to remove rows with null values.

If you want/need to keep the blank rows, you’ll need to replace the null values with 0 in at least one column before unpivoting.

NOTE: any column where you have not replaced the null with 0 will not show an entry for that value.

3

@Heather Got you, That helps :slight_smile:

Thank you.