Gen2 Dataflows - Again appear to have serious issues.

[MartinMason]

So there's this function that does nothing more than convert a date to a Timecard Period Id. 

let 
  GetTimeCardPeriodId = (DateToDetermine as date) => let
    ElapsedDays = Duration.Days(DateToDetermine - #date(2017, 8, 27)),
    TimeCardPeriodId = if ElapsedDays < 0 then null else Number.IntegerDivide(ElapsedDays, 14) + 1
  in
    TimeCardPeriodId
in 
  GetTimeCardPeriodId

The function is not referenced anywhere else in the dataflow now because my frustration level has reached an epic level. When the dataflow executes, the dataflow fails because it looks like the Gen2 Dataflow is trying to save the function output to .... somewhare. The following error is returned. 

The error returned on this "function" is the following.

104100 Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: We can't convert values of this type to JSON. Details: Reason = Expression.Error;Microsoft.Data.Mashup.Error.Context = User

I'm going to be fired for choosing Fabric. As our data resides on on-prem SQL Servers, Gen2 Dataflows are our only option. And this technology is truly twice as crappy as Gen1 dataflows. 

Can someone help? What am I doing incorrectly? The same dataflow has another function that generates a generic date dimension table that doesn't cause any issues. The only difference between the two functions is that one was written seven months ago when Gen2 Dataflows probably didn't have an error that they now probably contain.

[Anonymous]

Hi @MartinMason ,

Thanks for using Fabric Community.

Apologies for the issue you have been facing. I would like to check are you still facing this issue? 

It's difficult to tell what could be the reason for this performance.

I'd highly recommend reaching out to our support team by raising a support ticket so an engineer can take a closer look at your situation:

https://support.fabric.microsoft.com/support

[MartinMason · Accepted solution]

I created a support ticket, 2402060040005927. However, after investigating, think I have identified the root cause. When creating a function, I'm adding a Blank Query and then pasting the M code into the Blank Query definition. That apparently is the root cause. And from that point on, I'm toast. When returning to the dataflow now, the Enable Staging option on that newly created function is disabled, but.....is apparently set to true. And bam, the error is there and cannot be resolved.

To create a function correctly, add a Blank Query and go through without changing. When in the dataflow screen, after creating the let Source = "" in Source query, uncheck the Enable Staging option. Then edit that query with the function definition.

It would be interesting to see if someone else sees the same behaviour in their Fabric tenant.

[Timal]

Hey,

Thanks man, you've saved me a lot of time.

Apprently this seems to be an issue (again?) still.

I was getting this issue while using a query as variable e.g.

let

 Source = Date.Month(Date.AddMonths(Date.From(DateTime.LocalNow()), -1))

in

 Source

​Staging is enabled by Default when creating a blank query but i was in Fact able to just disable staging and fix the issue.However in the PowerQuery Editor it obviously worked fine and just as soon as i hit refresh it published successfully but failed loading.

TLDR; if you're using a custom (blank) query to output anything else than an actual Table you have to disable Staging

[Element115]

Nope... still works on my end... publish+refresh and no errors. 

[Element115]

That makes two of us, frustration-wise.  Testing right now my DF that contains the DimTable generating function...

[Anonymous]

Hi @MartinMason ,

I would like to check once again with you, whether your issue was resolved? Or still support team is looking into your issue?

Incase if any help is required from my end, let me know.

Thanks,
Gopi Krishna Chenna

[MartinMason]

It's been resolved. It's impossible to create the situation I was running into two days ago because now when you try to publish a dataflow and a function is created with Enable Staging checked (option was disabled for functions two days ago), you get an error that at least one of the queries in the dataflow does not contain a schema.

[Anonymous]

Hi @MartinMason ,

Glad to know your query got resolved.
Please continue using Fabric Community for your further queries.