Forum Discussion
nhoff
Advocate I
Advocate IUnpivot removes rows with no/null values - how to keep them?
When I unpivot a table, the rows with no data gets removed. Any suggestions for how to keep the columns of rows, that are no unpivoted? In the example below, notice that Andrew has no sales and i...
Select Monday to Friday in Query Editor, Go to Transform Tab, Replace Values ---> Replace "null" with "0".
You would see the expected results.
Nick,
You probably thought of this already:
1) replace Null with 0 or "No Value"
2) unpivot
3) replace the 0 or "No Value" with Null
Exactly this! Although when you swap null dates for 0, unpivot, then convert data type back to date, Power Query converts 0 to 30/12/1899. This is easy enough to then filter out in any further transformations. But just as easy to convert 0 to null then change data type