Nulls and blanks handled inconsistently in dataflows Power Query editor

[JoshT]

I have dataflow that does a simple replace for blank values on a text column from a linked entity (in a premium workspace). When I use the below formula, the replacement displays as expected in the Power Query editor:

Table.ReplaceValue(#"Removed columns", "", "No modules", Replacer.ReplaceValue, {"Modules"})

However, when consuming this dataflow table in a downstream object, the replacement hasn't happened:

I tried replacing null, which does not do the replacement in the Power Query editor:

Table.ReplaceValue(#"Removed columns", null, "No modules", Replacer.ReplaceValue, {"Modules"})

But in downstream objects, the replacement has happened:

We have tried using the ReplaceText function instead, it doesn't replace "" in either, and throws a type conversion error when trying to convert null (as expected).

We have tried using an if statement to see if the problem is with the Replacer class, but seen the same results (replace null does not replace in editor, but does so in downstream object; replace "" does replace in editor, but doesn't in downstream object):

Table.AddColumn(#"Removed columns", "NewModules", each if [Modules] = "" then "No modules" else [Modules])

It appears that the display value in the Power Query editor is not consistent with whatever is stored in the dataflow once it is refreshed. It also doesn't make sense that a transformation on null works in the refresh given that the column is formatted as text, for which null is not a valid value. I believe the output of the Power Query editor should reflect what is stored when the dataflow refreshes.

This behaviour does not replicate when referencing a normal table in the query rather than a linked entity.

We are using the below versions:

Service version:13.0.22872.56

Client version:2403.2.18432-train

Desktop:2.126.1261.0 64-bit (February 2024)

[v-yetao1-msft]

Hi @JoshT 

Thanks for your feedback! No other users have reported similar issues so far, so there are a couple issues I need to double check.

1. If your dataflow related to a normal table ,  "" can work both in Power Query and downstream object , right ?

2. If your dataflow related to a linked entity , you used "" , it  worked in Power Query but not in downstream object . You used null , it worked in downstream object but not in Power Query , right ?

Best Regards,
Community Support Team _ Ailsa Tao

[JoshT]

Hi @v-yetao1-msft ,

Yes, both statements are correct. This issue was reported to me by one of my users and I've been able to reproduce it successfully by creating a linked entity against their source dataflow (which extracts data from an API). I tried to reproduce from scratch by setting up my own simple dataflow with a user input table, creating a linked entity to that in a separate dataflow, then doing the replacements but not getting the same results (i.e. downstream entity is showing tables as expected).

Unless you have any further ideas I may need to investigate further when I'm back at work in a couple of days.

Thanks

Josh