Forum Discussion
Helper IFabric - Lookup don't' work with Filter and ForEach - Nested Json
I “migrated” a fabric project at a client and found this situation:
The lookup returns a nested json array instead of a json array yet it reads from a lakehouse table (both without schema) the same data and has the same format.
Could anyone tell me if Fabric has different versions or if there are configurations on Azure that impact Fabric ?
New Fabric Project - Nested Json - don't work
{
"name": "Source_Table",
"value": [[
{
"KeyID": 0,
"Source_Schema": "stg",
"Source_Table": "Assembly",
"Destination_Schema": "dbo",
"Destination_File": "Assembly",
"Destination_Table": "Assembly",
"Fields_Filter": "$systemModifiedAt",
"Last_Update": "2000-01-01",
"Import_Type": 1,
"isActive": 1
},
{
"KeyID": 91,
"Source_Schema": "stg",
"Source_Table": "User",
"Destination_Schema": "dbo",
"Destination_File": "User",
"Destination_Table": "User",
"Fields_Filter": "$systemModifiedAt",
"Last_Update": "2000-01-01",
"Import_Type": 1,
"isActive": 1
}]]}
Project - Array json - work
{
"name": "Source_Table",
"value": [
{
"KeyID": 0,
"Source_Schema": "stg",
"Source_Table": "Assembly",
"Destination_Schema": "dbo",
"Destination_File": "Assembly",
"Destination_Table": "Assembly",
"Fields_Filter": "$systemModifiedAt",
"Last_Update": "2000-01-01",
"Import_Type": 1,
"isActive": 1
},
{
"KeyID": 91,
"Source_Schema": "stg",
"Source_Table": "User View",
"Destination_Schema": "dbo",
"Destination_File": "User View",
"Destination_Table": "User",
"Fields_Filter": "$systemModifiedAt",
"Last_Update": "2000-01-01",
"Import_Type": 1,
"isActive": 1
}]}
Flow created where I take data from a lakehouse delta table with lookup I set a variable filter the data and pass the json to Foreach.
The flow stops at the filter because it doesn't read the json correctly
Hi giupegiupe ,
In this scenario i suggest you to raise a support ticket here. so, that they can assit you in addressing the issue you are facing. please follow below link on how to raise a support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
thanks,
Prashanth Are
MS fabric community support
11 Replies
- burakkaragoz
Super User
Hi giupegiupe ,
Yeah, I’ve run into something similar before. When you're working with nested JSON structures—especially ones coming from sources like Azure Data Lake or Dataverse without a defined schema—Fabric sometimes struggles with resolving lookups inside foreach loops, particularly when filters are applied on nested arrays.
From what you shared, it looks like the issue is that the lookup_table is nested too deep, and the filter logic can’t properly resolve the path during runtime. This usually happens when the schema isn’t explicitly defined, so Fabric can’t infer the structure well enough to apply the filter correctly.
A couple of things you might try:
- Flatten the JSON before passing it into the foreach. You can do this by transforming the structure upstream (e.g., in a Dataflow or Power Query step).
- If flattening isn’t an option, try using select or map to isolate the nested array before applying the filter. That sometimes helps Fabric resolve the path better.
- Also, make sure your filter expression is referencing the correct path. Sometimes even a small mismatch in the path (like missing a level) causes the filter to silently fail.
Let me know if you want help rewriting the JSON or filter logic—happy to take a look.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
giupegiupeI thank you and really appreciated your answer but probably the problem is not in the schemaas I suffer a table read (same in the two tenants) from a lookup but is it possible from a dfferent “configuration” of the tenant?
The problem probably comes from the lookup reading a table with a dozen or so fields. What amazes me is that the same read (direct table no filter no schema no other situations) on two different workspaces and two different subscription and in two different tenats behaves differently, two different json, and yet it, lookup activity and lakehouse, is a standard object.
This means that in the tenant where the lookup doesn't work you can't use direct filter or foreach unless as you say you “flatten” the json (which I did with a notebook).
What I can't understand is how can a standard object reading a lakehouse table have two different behaviors on two different tentants.
The only explanation I can come up with is that there is something in the tenant that affects fabric? different fabric vesion ? Free trial ?
Lookup read a table in a lakehouse- burakkaragoz
giupegiupe ,You're absolutely right: when your JSON sources come from multiple configurations or schema variants, Fabric may interpret the structure differently depending on the metadata it reads at runtime.
This happens because:
- Fabric tries to infer schema dynamically, and if the structure varies (even slightly) across records, it treats them as different shapes.
- So even if a field exists logically in all records, Fabric might not resolve it properly during a lookup or filter if it’s nested differently or missing in some samples.
Here’s what I’d suggest:
- Normalize the schema upstream if possible — either by flattening or by enforcing a consistent structure (e.g. using a Dataflow with schema projection).
- If that’s not possible, try using Record.FieldOrDefault() or Record.HasFields() in Power Query to safely access nested fields without breaking the query.
- Also, consider splitting the ingestion into separate flows per configuration, then unifying them after schema alignment.
This kind of issue is common when working with semi-structured data — especially when schema-on-read is involved.
- giupegiupe
I thank you and really appreciated your answer but probably the problem is not in the schemaas I suffer a table read (same in the two tenants) from a lookup but is it possible from a dfferent “configuration” of the tenant?
The problem probably comes from the lookup reading a table with a dozen or so fields. What amazes me is that the same read (direct table no filter no schema no other situations) on two different workspaces and two different subscription and in two different tenats behaves differently, two different json, and yet it, lookup activity and lakehouse, is a standard object.
This means that in the tenant where the lookup doesn't work you can't use direct filter or foreach unless as you say you “flatten” the json (which I did with a notebook).
What I can't understand is how can a standard object reading a lakehouse table have two different behaviors on two different tentants.
The only explanation I can come up with is that there is something in the tenant that affects fabric? different fabric vesion ? Free trial ?
Lookup read a table in a lakehouse 
v-prasareCommunity Support
giupegiupe As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
burakkaragoz Thanks for your prompt response
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
- giupegiupe
The solution works, as I wrote previously, if I do it with a notebook but it does not work using Fabric objects.
I cannot pass the information from a lookup to a variable to a filter or foreach for this fabric tenant, or directly from a lookup to a filter or ForEach.So it is not clear why the jason array does not work, instead creating a nesetd array jason, in this tenant but on others tenants it works correctly
Thanks
Giuseppe- v-prasare
Hi giupegiupe ,
In this scenario i suggest you to raise a support ticket here. so, that they can assit you in addressing the issue you are facing. please follow below link on how to raise a support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
thanks,
Prashanth Are
MS fabric community support
