Dataflow Gen2: Variable Library references fail in Connection Settings

[AntoineW]

Hi everyone,

I am working on variabilizing my Dataflow Gen2 for deployment pipelines, following the documentation on Variable references.

I’ve noticed a discrepancy in how variables are handled, and I suspect it might be a bug or a UI limitation.

The Issue: When I define parameters using a Blank Query and try to use them to replace values in the Source Connection settings (via the user interface), the connection fails or isn't recognized.

And replace in the source:

let
Source = Lakehouse.Contents([]),
#"Navigation 1" = Source{[workspaceId = workspace_id]}[Data],
#"Navigation 2" = #"Navigation 1"{[lakehouseId = lakehouse_id]}[Data],
#"Navigation 3" = #"Navigation 2"{[Id = "Table1", ItemKind = "Table"]}[Data]
in
#"Navigation 3"

That's not working.

The Workaround (Manual M Code): However, if I bypass the UI and manually insert the Variable.ValueOrDefault syntax directly into the Source Step using the Advanced Editor, it works perfectly.

let
Source = Lakehouse.Contents([]),
// Retrieving variables manually works fine
workspace_id = Variable.ValueOrDefault("$(/**/My Library/Workspace ID)", "default_value"),
lakehouse_id = Variable.ValueOrDefault("$(/**/My Library/Lakehouse ID)", "default_value"),

#"Navigation 1" = Source{[workspaceId = workspace_id]}[Data],
#"Navigation 2" = #"Navigation 1"{[lakehouseId = lakehouse_id]}[Data],
#"Navigation 3" = #"Navigation 2"{[Id = "Table1", ItemKind = "Table"]}[Data]
in
#"Navigation 3"

My Question: Has anyone else encountered this issue where the UI connection dialog doesn't seem to parse the variable reference, forcing us to hardcode M logic manually? Is there a specific syntax for the UI that differs from the Advanced Editor?

Thanks for your help!

References:
- Dataflow Gen2 Variable Library Integration

[miguel]

Would you mind sharing a screenshot of the error or failure that you're seeing ?

[AntoineW]

I have this : 

In my Advanced Editor query, I have several transformation steps (adding new columns, renaming, etc.) and a merge with other queries.

When I update the parameters using the Variable Library, all transformation steps work correctly, except the Merge step, which fails.
The merge returns the following error: “DataFormat.Error: There is no valid Delta table at this location.”

[miguel · Accepted solution]

This appears to be a screenshot of an error shown in the data preview of the Power Query editor.

When using Variable.ValueOrDefault the value being used for the data preview today is the "Default". What could be happening is something related to the value that you have as default and how that is being passed into the M code. Perhaps there's something quite specific with the Lakehouse connector that returns this error or it could also be that your queries where you store your variables are set to staging hence it's actually not a scalar value but rather a table. Based on the screenshot, it does appear that both your queries are set to be staged hence it might actually field during a refresh as the values wouldn't be rendered as simple values and they'll be basically just a value within a table of a single column 

Regardless, I'd recommend opening a support ticket so we can take a closer look at this behavior. Feel free to share the case number with me directly so I can take a closer look