Unpivot Column

[Jenil_Gala]

Hi,

I am trying to unpivot columns in transform/power query, but its disappearing some rows from that column. Can you help me why its removing these rows?

[MarkLaf · Accepted solution]

Unpivot will drop nulls. If you want to keep the nulls, the only way I know of is to replace all nulls with some placeholder character/value (I'm partial to: ☃ ), unpivot, then switch back your placeholders to null.

If that isn't the issue, please provide some sample data that recreates your issue.

[v-pnaroju-msft · Accepted solution]

Thankyou, @MarkLaf, for your response.

Hi Jenil_Gala,

We sincerely appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding, the issue you are facing arises because blank cells in Excel are interpreted as null values in Power Query. When using the Unpivot Columns feature, rows containing null values in the unpivoted column are automatically removed by default.

Kindly follow the procedure outlined below to retain the rows with null values:

1. In Power Query, select the columns you wish to unpivot. Use the Replace Values option to substitute null values with a placeholder (for example, “_NULL_”). Then unpivot the columns. Afterward, replace the placeholder “_NULL_” back to null.

Additionally, please refer to the following links for more information:
Replace values and errors - Power Query | Microsoft Learn
Unpivot columns - Power Query | Microsoft Learn

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries. Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.

[Jenil_Gala]

Hi,

Thanks for reply. I think it was the blanks in my excel data that was causing issues. You are right. 

[MarkLaf · Accepted solution]

Unpivot will drop nulls. If you want to keep the nulls, the only way I know of is to replace all nulls with some placeholder character/value (I'm partial to: ☃ ), unpivot, then switch back your placeholders to null.

If that isn't the issue, please provide some sample data that recreates your issue.