Expression.Error: We cannot apply operator & to types Number and Record

[xixo221]

What do I want?

I would like to create veeeery basic dataflow and report/dashboard/whatever just to verificate the proof of concept, because my company is currently evaluating which reporting solution to use.

How did I get here?

I used PowerBI service for the first time (currently in free trial version). I did not fiddle with any settings.

1) Created and connected my on-premise data gateway to PBI

2) In my workspace I created new dataflow

3) selected data source for my dataflow

(I also checked Navigate using full hierarchy option in advanced options)

4) selected a few tables (doesnt matter which) and clicked Create. As you can see the connection with gateway is working as I can see the DB data.

As you can see I am not doing any transformations. I only selected tables and published dataflow right after.

5) I click on publish button

6) Dataflow gets published and starts to refresh right after, but it always fails in the end (takes roughly 5 minutes).

(btw Error message is partially in my local language even though I explicitly set PowerBI language to english)

Here is the error msg (I translated non english parts to english):

sale_order: Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Table insertion failed., InnerException: We cannot apply operator & with types Number a Record., basic error: We cannot apply operator & with types Number a Record. Details: Reason = Expression.Error;Message = We cannot apply operator & with types Number a Record.;Detail = [Operator = "&", Left = 2, Right = [...]];Microsoft.Data.Mashup.Error.Context = System

I tried this multiple times even with different tables. But it always fails. (failing tables change).

I honestly dont understand the error at all. I didnt do any transformations and I dont have any Record type columns in my tables.

And the even weirder thing is that if I have only two tables (even if it were two tables that failed beforehand) in my dataflow then everything works, but as soon as I add more than 2, then I get the error.

Additional info

Database (Postgres 15) and data gateway are on the same computer. DB runs in docker with forwarded ports.

You can see here that I dont have any Record type columns.

[mrozzano]

@xixo221, our issue might be related to this :

Solved: Re: Dataflow Gen2 Start to fails after Enterprise ... - Microsoft Fabric Community

[mrozzano]

I started getting this same error, too.

I did some on-demand refreshes a few minutes apart for a simple dataflow. The failures appear random, but all have the same error (listed at the bottom).

The tables listed above are a duplicate and reference of a table that pushes to a lakehouse. I wasn't sure if the dataflow failed because the tables were reference tables vs duplicate tables so i created both types to test. But, it doesn't matter. As the screen shots show, either or both can fail randomly. 

When I disable staging on these tables, the dataflow refreshes successfully. However, that means I can't use Get data > Dataflows to select these tables, defeating the purpose of "Enable staging".

This dataflow refreshed without errors previously. Is something broken behind the scenes or am I doing something wrong ? 

Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Failed to insert a table., InnerException: We cannot apply operator & to types Number and Record., Underlying error: We cannot apply operator & to types Number and Record. Details: Reason = Expression.Error;Message = We cannot apply operator & to types Number and Record.;Detail = [Operator = "&", Left = 2, Right = [...]];Message.Format = We cannot apply operator #{0} to types #{1} and #{2}.;Message.Parameters = {"&", "Number", "Record"};Microsoft.Data.Mashup.Error.Context = System GatewayObjectId: ____ (Request ID: ____).

[EcurieArgyll]

I also experience this same error and it does appear to be random in nature. My data originates in on premises SQL Server, so I don't think it is related to Postgres as suggested elsewhere.

In fact, the latest instance of this error for me is against my central date dimension table which is created entirely within Fabric from a Power M script.

I do not explictly do what the error message describes, so I believe it is a consequential error from something internal.

I did revisit some of the dataflows (gen2) that were failing and when I explored the destination details, it had the message about some of the data types having changed, but they hadn't. 

I'm afraid I have no concrete suggestions other than I have found that revisiting a dataflow and re-confirming the destination details "can" help.

[mrozzano]

Hi @EcurieArgyll. see my post above to this post.

This problem appears to be related to the 2024 April version of the gateway. My team downgraded the gateway to the 2024 February version and that solved the problem. And based on the post I linked to, installing the May version also fixes this problem.

[Greg_Deckler]

@xixo221 This seems very much like a bug or some weird limitation with Postgres. I would submit it to the Issues forum: https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues