Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
5 years ago

How to append table in Direct query to NOT result import mode

Hi,

As the question, I need to append an exact table, but still I need this to be Direct query mode. Understood that it always result Import mode, but is there any workaround ? 

Because my system really not allowing me to have Import mode table. Both of the source table is Direct Query, btw.

 

Thanks.

Tips and Tricks

3 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    you can in the data model view change it to dual mode, but finally it will be both mode at the same time direct query and import mode, thiw will allow you to use the power query functions on this table. 

    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy
      5 years ago

      Hi all,

       

      Thanks for all the suggestion. Unfortunately my 'target system' (Dynamics 365 F&O) only allowed 'pure' Direct Query, so I think there is no workaround on this, Union will always result new table which will be on Import mode. Dual mode is not allowed.

       

      So I need to create from the source DB instead.

       

      Thanks again. 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, admin_xlsior 

     

    As is mentioned in the document , each Directquery mode table must be based on a Power Query query that can be folded. 

    Generally, the following list describes transformations that prevent query folding. This is not intended to be an exhaustive list.

    • Merging queries based on different sources.

    • Appending (union-ing) queries based on different sources.

    • Adding custom columns with complex logic. Complex logic implies the use of M functions that have no equivalent functions in the data source. For example, the following expressions formats the OrderDate column value (to return a text value).

       

    • Adding index columns.

    • Changing a column data type.

     

    As a workround, you may try create a calculated table with dax as below.

     

    Table = 
UNION(
    New,
    Student
)

     

    Result:

     

    Best Regards

    Allan

     

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