Dataflow Gen2 w/ Variable Library - variable resolves but fails in destination query

[merganzeruncle]

I'm trying to create a Dataflow Gen2 that uses a variable library to remap the destination lakehouse between stages of a deployment pipeline. I followed these two guides:

https://learn.microsoft.com/en-us/fabric/cicd/variable-library/tutorial-variable-library

https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-variable-library-integration

I have created a variable library with entries for WorkspaceId and a LakehouseId. 

I have two queries for WorkspaceId and LakehouseId in my Dataflow. These appear to be working. Just as a test, I added another query that writes these to a (hardcoded) lakehouse destination, and the data that appears when I run this is correct (the correct Workspace Id and Lakehouse Id).  

However, when I try to use WorkspaceId and LakehouseId in the destination query of one of my actual data queries, I get an error "The key didn't match any rows in the table".  

Here is the m script for the destination query that fails: 

let
  Pattern = Lakehouse.Contents([CreateNavigationProperties = false, EnableFolding = false, HierarchicalNavigation = true]),
  Navigation_1 = Pattern{[workspaceId = WorkspaceId]}[Data],
  Navigation_2 = Navigation_1{[lakehouseId = LakehouseId]}[Data],
  Navigation_3 = Navigation_2{[Id = "Strong", ItemKind = "Schema"]}[Data],
  TableNavigation = Navigation_3{[Id = "Destination", ItemKind = "Table"]}[Data]
in
  TableNavigation<p>

(To clarify, that ID="Destination" is because the destination table name is also incidentally called "Destination" for unrelated reasons)

This was generated by using the destination lakehouse widget on the query, but then I replaced the hardcoded workspace Id and lakehouse Id with my variables. Other potentially relevant information - the destination lakehouse is schema-enabled. These queries don't use incremental refresh, although I have another Dataflow that I need to do this in that does use an incremental refresh.

[Tamanchu]

Hi @merganzeruncle,

This is a known gotcha with Variable Library + Lakehouse.Contents navigation. The error "The key didn't match any rows in the table" almost always means one of two things:

1. GUID case mismatch (most likely)

The navigation table returned by Lakehouse.Contents uses lowercase GUIDs, but the variable library sometimes stores/returns them in uppercase or mixed case. The comparison is case-sensitive.

Fix wrap your variables with Text.Lower and Text.Trim :

let
Pattern = Lakehouse.Contents([CreateNavigationProperties = false, EnableFolding = false, HierarchicalNavigation = true]),
Navigation_1 = Pattern{[workspaceId = Text.Lower(Text.Trim(WorkspaceId))]}[Data],
Navigation_2 = Navigation_1{[lakehouseId = Text.Lower(Text.Trim(LakehouseId))]}[Data],
Navigation_3 = Navigation_2{[Id = "Strong", ItemKind = "Schema"]}[Data],
TableNavigation = Navigation_3{[Id = "Destination", ItemKind = "Table"]}[Data]
in
TableNavigation

2. Variable returns a table, not a scalar

If your WorkspaceId query returns a single-row table instead of a plain text value, the navigation lookup will also fail. To verify, check that your variable query ends with something like:
= VariableLibrary{[Name = "WorkspaceId"]}[Value]

...returning a plain text scalar, not a table.

Quick diagnostic: Add a temporary query = Text.Lower(WorkspaceId) if it errors, your variable is not a scalar yet.

Note: since your lakehouse is schema-enabled, the {[Id = "Strong", ItemKind = "Schema"]} step looks correct, assuming "Strong" is the schema name. Double-check this matches the actual schema name in your lakehouse.

Hope this unblocks you

[merganzeruncle]

Thank you! This was very helpful.

I think it is actually the second one - when I try normalizing the LakehouseId and WorkspaceId variables, I get an error that says that the input is a table and not a scalar. 

The syntax you suggested: VariableLibrary{[Name = "WorkspaceId"]}[Value] isn't what's in the guides - I'm using this syntax:
Variable.Value("$(/**/Env/[VariableName])"). How do I refer to my specific Variable Library using the syntax you have written? It doesn't work to replace "VariableLibrary" with my library name - how do I specify the library name?

[merganzeruncle · Accepted solution]

Update - this was the issue. My query that got the variable value used the Variable.Value function - this was the whole script (my variable library name is just called "Env"):

Variable.Value("$(/**/Env/WorkspaceId)")

This was returning a table, not a scalar. When I tried to convert the output to lowercase as suggested, this caused an error, which tipped me off that it was returning a table.

My variable query had staging enabled (see below) by default. I read that the Variable.Value function returns a single-column table for staging queries, so I turned off staging and this resolved the issue.