Forum Discussion

karo's avatar
karo
Advocate V
6 years ago

Keep Nulls when Unpivoting in Direct Query

Hi,

 

I would like to keep records with Null values when Unpivitoing data in Direct Query Mode. In Import Mode I can use Replace Values (null -> 0) and it works perfectly, but direct query mode does not support Replace Values.

Any hints, please?

 

Regards,

Karo

 

Need Help
Tips and Tricks

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    When using direct query, you have many limitations for data transformation within Power BI.

    I am afraid you have to create this transformation in your database instead or use Import Mode.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, indeed, replace does not work. But AddColumn works with DirectQuery:

    NewTable = Table.AddColumn(OldTable, "NewDim", each if [OldDim] = null then "null" else [OldDim])

    This way, you replace your null values by the string "null", now you can pivot, and even change it back to null later if you like.

    Alexander

    If this solves your problem, please accept as solution.  

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi karo ,

     

    Yes, this is the limitation in the direct query mode.

    Change to import mode or create a new column using m or DAX.

    DAX formula may like this:

    Column = if (isblank( 'table' [xxxx]),  0 , 'table' [xxxx])

    Sometimes you need to change that column to text format first.

     

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

    • karo's avatar
      karo
      Advocate V
      6 years ago

      Hi V-lianl-msft , Anonymous ,

       

      Thank you for idea, however I am afraid that creating new columns is not an option in my case. I have 24 columns to unpivot and most probably such action would have significant negative impact on report performance 😞

       

      Any other ideas, instead of CR to SQL database as Anonymous  suggested above?

       

      Regards,

      Karo

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi karo 

        I see. If your 24 columns are all numbers, you can add zero to each of the columns. If it´s text, you can add "" to each. However, like in the previously mentioned approach, you need to repeat this for each column. You can do it in a single line of code if you edit it manually:

        NewTable= Table.TransformColumns(#"OldTable", {{"col1", each _ + 0, type number}, {"col2", each _ + 0, type number}})

        But in case you´ll have 25 columns someday in the future, you´ll need to adjust the code.

        Alexander

        If this solves your problem, please accept as solution.