Forum Discussion

sheetalshettiga's avatar
sheetalshettiga
Icon for Helper IV rankHelper IV
5 years ago
Solved

Error on apply query changes in directQuery

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 giv...
Need Help
Show and Tell
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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

sheetalshettiga's avatar
sheetalshettiga
Icon for Helper IV rankHelper IV
5 years ago

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"