Power BI Dataflow with column type text having null value is rendered as blank in dataset

[Anonymous]

I have observed one thhing about Power BI dataflows. Entity having column as datatype Text while has null values in it. When i Get Data from that Dataflow  in Power BI desktop that column value is rendered as blank in dataset. 

I have noticed its for coulm with datatypoe text It works well for Number value.

Can someone please advise if this is an issue with dataflows

[v-yuta-msft]

@Anonymous ,

Based on my test, if you don't input any value in the column the value will be null in number format column and woll be blank in text format column both in power bi desktop and dataflow.

The workaround is to input "null" manually in the text format column.

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy

This is not the case with me.

Dataflow is showing the text column as null but when i fetch that field in dataset in power BI desktop it makes it blank.

If i query data directly in Power BI desktop by using SQL database as source it still shows the null not blank.

Regards

Vipin

[quichejackson]

Same problem, I have also replaced 'blank' with 'null' and the text columns import null values as blank.

[robert-murray]

Same. I can build the exact same queries/enties in PBI Desktop and get one result (null values recognized), do the same in a dataflow and get another (null values = blank). Not great as I prefer to do most of my work in PBI Desktop first, then copy to dataflow. 

[Anonymous]

Same issue here. When I view the preview query results for the Dataflow in Power BI Service, I can see the text fields are populated as with "null", but when I import the Dataflow into Power BI desktop, the null values are rendered as empty/blank, which causes problems in my Power BI report.

[Anonymous]

@v-yuta-msft what do you mean by 

"The workaround is to input "null" manually in the text format column."

What change do I need to make in dataflow?  I've tried replacing "" into
null - nothing changes;
"null" - nothing changes;
"(null)" - nothing changes;
I'm not interested in making this change on report side because I need dataflow to be universal

[PowerBIUser9901]

I have also found this to be the case. What's frustrating is that when a data flow entity shows null values for a text column and then you pull that entity (to create a linked entity ) in a separate data flow it comes up as blanks rather than null values.

In other cases, I've had dataflow change a text column into date type when pulling as a linked entity.

Microsoft please fix these issues, data types and values should not be changed when pulling entities from data flows.

[AJH]

This looks like a bug.

[jmkirkp]

Same problem, moving a table from 

<sql server> -> <pbix via import mode> null value remains null in PBI

<sql server> -> <dataflow> -> <pbix> null value is removed and changed to ""

Fortunately, I've just converted one table to a dataflow, but if I were to move my entire process over to a dataflow, I would have to validate a ton of logic since null and empty are handled differently.

[daanhumble]

A bit late to the party, but..

When you ingest data from dataflows, you do indeed get blank values instead of 'null'-values. It's not a bug, as it is documented as an unsupported datatype. That's not clear when you are creating a dataflow, as then you can choose and see the nulls in your data. 

When you use a dataflow to write data to a datamart, and use that datamart to connect to Power BI, than you do get null-values. This has probably something to do with the way the data is stored: when you write to a datamart data is stored in an Azure SQL db, which supports null-values. When you only use the dataflow, data is stored in a text file on a blob-store. There a null value is just an empty string. 

Unfortunately that leads to only one solution when working directly with dataflows to Power BI: transform all the blank values to nulls when you need them. You can use a Power Query statement like this one to do that for an entire table at once: 

BlankToNull = Table.ReplaceValue(#"Previous Step", "" ,null,Replacer.ReplaceValue, Table.ColumnNames(#"Previous Step"))