Unable to merge two tables in power query one with direct query and one import

[emurtini]

Hi all, 

I am looking for advise how to solve the issue below.

I able to merge two tables. Table A from direct query while table B from import and expand the tables couple of days ago. But now we can not merge the tables but we can not expand it. It said the query is not folding

First I did : Table.NestedJoin(Source, {"A"}, Y, {"A"}, "Y", JoinKind.LeftOuter).

Then I did: Table.ExpandTableColumn(#"Merged queries", "A", {"B", "C", "D", "E", "F"}, {"B", "C", "D", "E", "F"})

 I am not sure what is the cause and how to solve it.

Thank you.

[v-pnaroju-msft]

Hi emurtini,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi emurtini,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[Shahid12523]

The issue you’re facing is because Power BI does not allow you to directly merge/join a table that comes from a DirectQuery source with a table that comes from Import mode. That’s why it worked earlier (maybe both were in Import or both were in DirectQuery) but now you’re hitting the query folding limitation.

If you just need fields from both, try skipping the Table.NestedJoin step and instead load both into the model → then connect them in Model view with relationships.

[v-pnaroju-msft]

Thankyou, @AnkitKukreja, for your response.

Hi emurtini,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @AnkitKukreja  to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[AnkitKukreja · Accepted solution]

Hi! @emurtini 

Folding requires all operations to push down to the same source; once you mix Import and DirectQuery, the engine blocks it. It worked earlier (probably when both were in Import mode, or Power Query hadn’t refreshed the folding check yet).
So you can try few suggestions to work across.
1 - Make both tables to Import mode.
2 - Use composite model

• Keep one table DirectQuery and one Import.

• Don’t merge in Power Query. Instead: 

• Load both into the model separately.
• Define a relationship on column A between them.
• Use DAX measures or calculated columns to pull fields as needed.

3 - Make both tables DirectQuery (if possible).