Dataflow and Dataset treating null differently

[prathyoo]

We have a master dataset that was configured to connect to an Oracle database. Nearly 30 odd reports  have been built on this dataset (which has around 20 tables - dimensions and facts) using live connections.  Each table in power query is a SQL statement to the Oracle DB. Any filter or slicer that uses a column with null values show us as follows in the UI -

Null values in the db connected dataset shows up as the literal - "(Blank)" and we have been happy with this and we have also used measures with expressions like IsBlank without any issues.

Now we decided to separate the dataset into a dataflow and a dataset that connects to the dataflow. The power query in the dataflow was the original SQL statements from the above dataset. The new dataset that was created used power query to retrieve the dimensions and facts from the dataflow. Since every table and every column was an exact match, we could seamlessly replace the original dataset with the new dataset and all the reports using the live connections without any further changes. We tested each report and all the values seem to match but then we realized that null values are being treated differently now -

The null values in the original dataset that existed have now become empty strings in the dataset that connects to the dataflow. We decided to compare the power queries across the original dataset and dataflow and the new dataset (which uses the dataflow). Here are our finding for the above column -

Original DB Connected dataset - (this shows the null value as the literal "(null)")

The dataflow that uses this exact SQL - This also shows the literal "(null)". So far both are a match.

And here is the dataset that connects to this dataflow (in other words the csv in the internal power bi storage) - 

Here the literal "(null)" has been replaced with "(blank)"

Now we compared the tabular model of the dataset connected to db and the dataset connected to the dataflow. Now here is the surprising finding -

Original DB Connected dataset - the literal "(null)" in the power query was replaced as "(Blank)" in the tabular model.

And the dataset that connects to the dataflow - the literal "(blank)" in the power query has been replaced by an empty string.

Screenshots to follow.....the message board is not allowing me to add additional screenshots...

Now, because of this change in behaviour, looks like we will have to further transform all the power queries in the second dataset and replace all "(blank)" with "(null)" but that is what we wanted to avoid when using dataflows. That is, one team maintains the power query in the dataflow and every other team just consumes this.

[paul_sanders]

Just hit this issue as well.  It is appalling behavior, IMO, as it means that converting a PowerQuery straight to a source, to one going via a Dataflow, does not preserve behavior. In my case, rows were silently lost because I had filters on (null).  Even changing to reflect the obvious impact on filter then means need to consider possible impact everywhere else. Given that PowerQuery distinguishes Nulls and Space, storing as CSVs (with no special null handling) was maybe not the best of  ideas ! 

[prathyoo]

Here are the screenshots -

Original DB Connected dataset - the literal "(null)" in the power query was replaced as "(Blank)" in the tabular model -

And here is the dataset that connects to the dataflow - the literal "(blank)" in the power query has been replaced by an empty string.

[Greg_Deckler]

@edhans  ?

[strongCoreZach]

This is apparently still the case. Had a client that processed the same spreadsheet in dataflows vs desktop, with the same Mcode. Service processed as blanks, desktop returned null. To mirror the table in both, he had to processs for both null and blanks.

[prathyoo]

@Greg_Deckler   Thanks for taking notice of this issue. Would you be able to help us out. We have separately raised this with support and are waiting to see how that proceeds.

[Anonymous]

Hi, did you receive any reply from support on this behaviour?

[prathyoo]

Yes, but not a favorable one. The ticket was escalated to the product team. PG team said that dataflow tables are stored in csv files and as such have no difference conceptually between empty and null. Null and empty values from source are both saved as empty values. They have suggested that I need to add a step to modify null values from the source to "null" strings.

That means I would need to do this for every table that has a text column where there is a potential that the column could have blank values for some of the records.