Forum Discussion

nhoff's avatar
nhoff
Icon for Advocate I rankAdvocate I
10 years ago
Solved

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 i...
Need Help
  • BhaveshPatel's avatar
    BhaveshPatel
    10 years ago

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

     

    You would see the expected results.

nickvanmaele's avatar
nickvanmaele
Icon for Advocate II rankAdvocate II
9 years ago

Thank you for your time and suggestion. 

BradleyA's avatar
BradleyA
Regular Visitor
9 years ago

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's avatar
    Grumelo
    Icon for Advocate II rankAdvocate II
    8 years ago

    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 !

  • molegris's avatar
    molegris
    Icon for Advocate IV rankAdvocate IV
    4 years ago

    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.

  • searl's avatar
    searl
    Frequent Visitor
    4 years ago

    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