cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nhoff Regular Visitor
Regular Visitor

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

 

See screenshots and powerbi file

 

Before pivot

1.JPG

 

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

2.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
BhaveshPatel Super Contributor
Super Contributor

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

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

 

You would see the expected results.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
10 REPLIES 10
BhaveshPatel Super Contributor
Super Contributor

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

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

 

You would see the expected results.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
daneomite Frequent Visitor
Frequent Visitor

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

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

nickvanmaele Frequent Visitor
Frequent Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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

AJaso Occasional Visitor
Occasional Visitor

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

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

8) 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 Frequent Visitor
Frequent Visitor

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

Thank you for your time and suggestion. 

BradleyA Frequent Visitor
Frequent Visitor

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

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

Grumelo Regular Visitor
Regular Visitor

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

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 !

Highlighted
wynhopkins Member
Member

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

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 25 members 932 guests
Please welcome our newest community members: