Forum Discussion
Dataflow Gen2 w/ Variable Library - variable resolves but fails in destination query
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.
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.
3 Replies
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
TableNavigation2. 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
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?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.
