Unpivot removes rows with no/null values - how to keep them?

This worked a charm, thank you! 

After my unpivot I would like to replace my 0 back to null, can I confirm the following formula would work please?

#"ReplaceNulls2"=Table.TransformColumns("Unpivoted Other Columns1",{},(x) => Replacer.ReplaceValue(x,0,null))

Thank you.

What if preserving the NULL values is the required behaviour?

Context:

in a factory, different machines each store 30 parameter values internally. At the end of every shift (once every 8 hours) each machine writes its 30 parameters to a database table. The table has 3 key columns ("machine ID", "row ID" and "DateTime" which records the moment of writing into the table) and 30 numerical columns "param01" to "param30". 

The data writing process has different types of issues. I would like to count the number of bad data points for each type of issue

.

One issue causes correct numerical parameters in the machines to be written as NULL values in the database table.

(e.g. shift 1 could have param10 = NULL; shift 2 could have param10 = <correct value> but param17 = NULL; and so on. The parameter values of each data record are impacted more or less at random) (note: the 3 key columns are never NULL)

Hence I would like to count the occurrence of the "bad write" error type by counting all NULL values in the table. 

I followed the solution advised here, i.e. 

1. unpivot the 30 parameter columns (resulting table has 5 columns: the 3 key columns + "Parameter name" + "Value")
2. count all NULL values in "Value"

Problem:

Power Query does not create unpivoted rows if a parameter column has value NULL, i.e. no unpivoted row is created in this case. 

(e.g. if 20 out of 30 parameters are NULL in a record in the original table, the unpivoting operation results in 10 rows each having "Value" = <a number>, instead of my desired result of 30 rows where 10 row have a number and 20 rows have "Value" = NULL)

Constraints: 

1. Since a machine parameter can actually be 0 and valid, replacing NULL by 0 prior to unpivoting is not allowed. This would turn a bad data point into a good one and falsify my occurrence count for the "bad write" type of error. 
2. Replacing NULL with a non-occurring value (e.g. -999999) prior to unpivoting solves the problem for historical data but, given the range of issues, any such value could potentially appear in a future record and be caused by a different type of error (say of type "counter error"). If this ever occurs, I would in the future start to miscount the occurrences of the "bad write" error by +1 and the occurrences of "counter error" by -1. 
3. I cannot re-run the data output for the machines for dates older than 30 days so correct reconstruction of historical data is impossible. 

Question: 

Since NULL values give me unique information, is it possible to unpivot whilst preserving NULL values?

(i.e. in such a way that unpivoted rows are created with "Value" = NULL)

How can I account for new columns of data being added when using the replace “Null” with 0?  I’ve found that replace values does not pick up new columns when added to the table, thus, the unpivot removes the new column's rows with null values. Is there a way to make the replace values dynamic as new columns are added or to keep nulls through the unpivot process and then replace them when the values are in a single column?

I've only been able address this issue by manually adding the column name in the advanced editor's replace values code line.

Thanks,

Dane

What if my column contains dates?

Doing the unpivot without replacing "null" for "0", my column continues in the DATE format.

But doing the unpivot after replacing "null" for "0", my column comes in TEXT format. Making impossible for the calculated column I have to count the days.