Forum Discussion

sheetalshettiga's avatar
sheetalshettiga
Helper 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 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

 

 

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.

     

     

     

3 Replies

    • sheetalshettiga's avatar
      sheetalshettiga
      Helper 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"

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    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.