Error on apply query changes in directQuery

[sheetalshettiga]

Hi all,

I am creating a .pbix file by automation that is I am loading the metadata details to DataModelSchema and DataMashup file with metadata from json input and zipping back to .pbix.

When I give option as directquery with Merged tables I get error as shown below in attachment.

with import mode I dont get below error message with Merged tables

I have given only one data source to PRODUCT_CATEGORIES table still its shows this error

 please help me with this

[v-lionel-msft · Accepted solution]

Hi @sheetalshettiga ,

We can not merge tables with storage mode "DirectQuery" and from different data sources, but we can merge tables with storage mode "DirectQuery" and from the same data source.

--my sample

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@sheetalshettiga , I doubt you can you can use merge in the direct query, Can share details of the operations you have done?

[sheetalshettiga]

Hi @amitchandak 

I tested out merge Queries is happening mannually in power BI

Mquery for merge:

let
#"Source" = Oracle.Database("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))", [HierarchicalNavigation=true ]),
OT = #"Source"{[Schema="OT"]}[Data],
#"PRODUCT_CATEGORIES1" = OT{[Name="PRODUCT_CATEGORIES"]}[Data],
#"Merged Queries PRODUCT_CATEGORIES_CATEGORY_ID6" = Table.NestedJoin(#"PRODUCT_CATEGORIES1", {"CATEGORY_ID"}, PRODUCTS, {"CATEGORY_ID (PRODUCTS)"}, "PRODUCTS", JoinKind.Inner),
#"Expanded PRODUCTS 6" = Table.ExpandTableColumn(#"Merged Queries PRODUCT_CATEGORIES_CATEGORY_ID6", "PRODUCTS", {"PRODUCT_ID (PRODUCTS)","PRODUCT_NAME","DESCRIPTION","STANDARD_COST","LIST_PRICE","CATEGORY_ID (PRODUCTS)"}),
#"Merged Queries PRODUCT_CATEGORIES_PRODUCT_ID (PRODUCTS)39" = Table.NestedJoin(#"Expanded PRODUCTS 6", {"PRODUCT_ID (PRODUCTS)"}, ORDER_ITEMS, {"PRODUCT_ID"}, "ORDER_ITEMS", JoinKind.Inner),
#"Expanded ORDER_ITEMS 39" = Table.ExpandTableColumn(#"Merged Queries PRODUCT_CATEGORIES_PRODUCT_ID (PRODUCTS)39", "ORDER_ITEMS", {"ORDER_ID (ORDER_ITEMS)","ITEM_ID","PRODUCT_ID","QUANTITY","UNIT_PRICE"})
in
#"Expanded ORDER_ITEMS 39"