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