Keep Nulls when Unpivoting in Direct Query

[karo]

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

[V-lianl-msft]

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]

Hi @V-lianl-msft , @Alexander76877 ,

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

[Alexander76877]

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. 

[karo]

Hi @Alexander76877 ,

I have tried your solution and it seems that it does not transform null to 0 😞 Does it work for you?

Regards,

Karo

[Alexander76877]

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.  

[Anonymous]

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.