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

[nhoff]

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 is excluded after the unpivoting.

See screenshots and powerbi file

Before pivot

After pivot - where is Andrew that did not have any sales?

[Rickmaurinus]

Hi!

One would imagine there's an optional parameter available for the Table.Unpivot operation to keep null values. Yet there isn't. So the easiest alternative is to make use of a placeholder. Turn nulls into placeholders, unpivot, then turn back the values into nulls if required. 

You can follow these instructions: https://gorilla.bi/power-query/unpivot-and-keep-null-values/

It also shows a way that does not hardcode columns and is future proof if new columns are imported in the table. 

Cheers,

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[wynhopkins]

In order to replace nulls without referencing specific columns you can use this code....   then unpivot

Found this code

https://social.technet.microsoft.com/Forums/en-US/fc15fa9c-3f16-47cc-98fe-59f48f0a44b8/power-query-d...

Let
    Source = Table3,
    ReplaceNulls = Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,0))
in
    ReplaceNulls

[Anonymous]

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.

[BhaveshPatel · Accepted solution]

Select Monday to Friday in Query Editor, Go to Transform Tab, Replace Values ---> Replace "null" with "0".

You would see the expected results.

[Anonymous]

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.

[nickvanmaele]

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)

[richardnlove918]

I have exactly the same requirements/contraints as Nick  - PLEASE HELP US!

[daneomite]

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

[AJaso]

I figured out a way to account for new columns. It's not the prettiest solution but it works.

Create a blank query

1) Create a list: ={0..9999}  (The 9999 represents the max number of rows of data you think you might have)

2) Convert the list to table

3) Define the list as a parameter

In your original query that you want to unpivot

4) Add an index column (before unpivoting)

5) Duplicate the query twice

In the first duplicate query

6) Demote the headers

7) Transpose the table

😎 Select the first column and delete other columns. You now have a header column.

9) Add a custom column invoking the parameter

In the second duplicate query

10) Select all of the columns that you are not going to unpivot including the index column, remove other columns

11) Merge this query with the first duplicate. Select index column from this query and number column from first duplicate and merge with a left outer join.

Back in the original query

13) Unpivot your data like you normally would

12) Merge this query with the second duplicate query. Select both the index and header columns in each query and then merge with an all outer join.

Now you should have a solution that contains null values and does not need to be modified each time you add a column. I hope someone can come up with a better method but, for now, this is all I've got.

[nickvanmaele]

Thank you for your time and suggestion. 

[BradleyA]

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

[searl]

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

[molegris]

Careful thought!  If you apply the transformation suggested above by @BradleyA, then you loose some information.  In the source, 0 and nulls might provide different information.  After the transformation, 0 and nulls are now both null and it's impossible to reverse, nor to identify wchich one was what initialy.

[Grumelo]

I'm having the same issue.

Replacing null by a value is a workaround not the solution.

I don't see any reason to drop null values.

The column exists, the row must exist !