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

[JCole]

Did you resolve this issue?

I am experiencing the same: Text replacement appears successfully in Power Query editor, but not when the table is referenced from outside the Dataflow (as a Linked Entity, or via Excel, for example)

The behaviour occurs both when doing a replacer.replacetext on the original column and when adding the text replacement as a new column.

[JoshT]

@JCole - sadly not, this is just one of those ones that fixed itself, so we didn't get to find out what was causing it. I believe the easiest thing to try is export the dataflow to json, delete it, then recreate by importing the json

[JCole]

Thanks Josh. I did try that (importing to a separate Workspace as well) but no joy.

I ended up reading the untransformed table into an intermediate Dataflow, and performing the transformations there, but that does add an unnecessary stage in the process.