Gen2 fails while Gen1 works

[smpa01]

TLDR :  Gen2 fails for same query where Gen1 succeds

I am trying to build a gen2 df. I have  made a DEV function like following to be utilized in dfGen2 as insertion of PROD query result of to lakehouse will be required

//fnGetTimestamp
// returns timestamp such as 4/29/2025, 12:34:43.240 PM
()=>let
    Source = Sql.Database("xyz.datamart.fabric.microsoft.com","db_powerbiprodnam_abc"),
    Custom1 = Value.NativeQuery(Source,"select ---- as timestamp")[timestamp]{0}
in
    Custom1

//dim_coa
//dim coa has data destination as lakehouse_staging
let
    Custom = fnGetTimestamp(),
    Source = Excel.Workbook(Web.Contents("sp"), null, true),
    --------------------------------------------------------------
    -----------------------------------------------------------------
    #"Reordered Columns1" = Table.ReorderColumns(#"prevStep",{}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns1", "gl_sid", each some_pq_built_in),  
   #"Changed column type" = Table.AddColumn(#"Added Custom2", "time_from_server",each Custom, type datetime)
in
    #"Changed column type"

I am getting an error as following

dim_coa_WriteToDataDestination: There was a problem refreshing the dataflow: 'Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: We cannot convert a value of type Table to type Function., Underlying error: We cannot convert a value of type Table to type Function. Details: Reason = Expression.Error;ErrorCode = Lakehouse036;Message = We cannot convert a value of type Table to type Function.;Message.Format = We cannot convert a value of type #{0} to type #{1}.;Message.Parameters = {"Table", "Function"};ErrorCode = 10276;Microsoft.Data.Mashup.Error.Context = User'. Error code: 104100. (Request ID: b8ce6fdd-35d9-41f3-8879-ee33f5993249).

Can someone from microsoft please advise what is going on here? Why Gen1 works and Gen2 fails for the same function?

This is urgent, I would appreciate it if you can please advise on this as I need to decide whether to put in PROD or not.

BTW, I have also experimented with fabric DW, lakehouse (same sql endpoint) instead of datamart in gen2 df, nothing works. Strangely, it does not error out only time, when I disable staging and don't write to a destination.

@ImkeF @imkefeldmann @AlexisOlson

[miguel]

Are you able to share some repro steps on what to test?

I'm able to use a custom function, invoke it inside of another query and get things working without issues. I even tested with a data source function inside of another custom function and things still worked for me without the need for things to be in a Group / Folder.

[smpa01]

@miguel  follow this to reproduce

#1 create a function
//fnGetTimestamp
() =>
let
    Source = Sql.Database("abc.datamart.fabric.microsoft.com","def"),
    NativeResult = Value.NativeQuery(Source, "SELECT GETDATE() AS timestamp", null, [EnableFolding = true]),
    TimestampValue = Record.Field(NativeResult{0}, "timestamp")
in
    TimestampValue

#2. create a sharepoint query and invoke above on that query and try to insert into a lakehouse destination

#3. It failed for me at #2

what actually worked

#1 I was copying this function from my github repo as I have them for quick copy paste
## intead of copy paste, I ran the query as below and chose to convert to function from the context menu
let
    Source = Sql.Database("abc.datamart.fabric.microsoft.com","def"),
    NativeResult = Value.NativeQuery(Source, "SELECT GETDATE() AS timestamp", null, [EnableFolding = true]),
    TimestampValue = Record.Field(NativeResult{0}, "timestamp")
in
    TimestampValue

## once PQ converted the above into a function with blank parameters, I added parameters as required and adjusted the function.
## it also creates a grouping
## from this point onwards I had no issue invoking into other queries

#2. create a sharepoint query and invoke above on that query and try to insert into a lakehouse destination

#3. It failed for me at #2

[miguel · Accepted solution]

We were finally able to determine what the issue is and we have a fix coming in the next few weeks. 
The root cause of the issue is a bug where the function still has the "staging" setting to be enabled regardless if the UI is not showing it. You can verify this by going into the GIT of the Dataflow and seeing the json file for the metadata of the queries in your Dataflow.

A simple workaround is to either start with a blank query that doesn't have the staging enabled OR transform the function to a query, change the staging to be disabled and then transform your query back to a function.

[miguel]

Thank you for bringing this up! I was able to repro the issue and I'm engaging internally with my engineering team. Tough to say why this is behaving this way, but we'll definitely look into it.

Thanks again and please keep the feedback coming!

[smpa01]

@miguel  follow this to reproduce

#1 create a function
//fnGetTimestamp
() =>
let
    Source = Sql.Database("abc.datamart.fabric.microsoft.com","def"),
    NativeResult = Value.NativeQuery(Source, "SELECT GETDATE() AS timestamp", null, [EnableFolding = true]),
    TimestampValue = Record.Field(NativeResult{0}, "timestamp")
in
    TimestampValue

#2. create a sharepoint query and invoke above on that query and try to insert into a lakehouse destination

#3. It failed for me at #2

what actually worked

#1 I was copying this function from my github repo as I have them for quick copy paste
## instead of copy paste, I ran the query as below and chose to convert to function from the context menu

let
    Source = Sql.Database("abc.datamart.fabric.microsoft.com","def"),
    NativeResult = Value.NativeQuery(Source, "SELECT GETDATE() AS timestamp", null, [EnableFolding = true]),
    TimestampValue = Record.Field(NativeResult{0}, "timestamp")
in
    TimestampValue

## once PQ converted the above into a function with blank parameters, I added parameters as required and adjusted the function.
## it also creates a grouping
## from this point onwards, I had no issue invoking the function into other queries

[miguel]

In the context that you've shared, Gen1 and Gen2 behave the same way where, without setting a destination, both effectively load the data to the "dataflow storage" (effectively what we call staging in Gen2)

The M code of the coa query seems redacted, so its difficult to comment on what could be causing the issue. It does appear to be something related to that query and the data destination definition for it.

Some suggestions:

- If you require urgent and immediate assistance from Microsoft, please raise  support ticket so an engineer can reach out to you, collect the necessary logs and troubleshoot the issue 

- When using a Lakehouse as a destination, it is recommended to not set any of your queries to be staged 

- The error mentions that there's a piece in your query that tries to interpret a function as a table. It would be good to go deeper into your coa query and see where you may be invoking any sort of custom code or any sort of data types that perhaps are not supported by the Lakehouse.

- Are you using an existing Lakehouse ? Does the error repro with a different Lakehouse or perhaps a completely new table in the Lakehouse?

hope this helps!

[smpa01]

@miguel 

I figured it out and please test out on your side as well.

The following resolved the problem, i.e. putting fn into functions folder which is EXTREMELY WEIRD. dim_coa has lakehouse as destination.

() => let
    Source = Sql.Database(server,db),
    NativeResult = Value.NativeQuery(Source, "SELECT GETDATE() as timestamp", null, [EnableFolding = true]),
    TimestampValue = Record.Field(NativeResult{0}, "timestamp")
in
    TimestampValue