Forum Discussion

Sujit_Thakur's avatar
Sujit_Thakur
Solution Sage
5 years ago
Solved

SQL SERVER DIRECT QUERY

Dear All ,

My data source is SQL Server , which instance is created in RDS from AWS .

 

Now when I am importing a view from database  using direct query mode , till this everything is fine , but the time I do some additional steps for editing and performing operation on columns it gives a error that 

 

"This query results in a query which cannot be used in direct query , change to import mode" 

 

 

Please help

amitchandak 

 

 

Regards

Thakur Sujit 

 

 

 

 

Need Help
  • Sujit_Thakur's avatar
    Sujit_Thakur
    5 years ago

    Thanks all ,. for replying .
    After reading Power BI Documentation it was clear that like normal import mode it is not possible to work on data in power query editor , because as every time i move a slicer , PBI queries data on point from SQL Server.
    Hence what i did was i did the steps in python where i used to process data before taking it in power bi

    then it worked thanks all

     

4 Replies

  • NimaiAhluwalia's avatar
    NimaiAhluwalia
    Continued Contributor
    5 years ago

    Hello Sujit_Thakur 

     

    If you are using Direct Query please keep the following things noted when creating the model!

     

    The term modelling in this context means the act of refining and enriching the raw data, as part of authoring a report using it. Examples include:

    • Defining relationships between tables
    • Adding new calculations (calculated columns and measures)
    • Renaming and hiding columns and measures
    • Defining hierarchies
    • Defining the formatting, default summarization and sort order for a column
    • Grouping or clustering values

     

    You are getting this error due to some limitations you are trying to use with Direct query.

     

    For more reference refer to this

    https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

     

    IF MY SOLUTION HELPED YOU IN SOME OR THE OTHER WAY PLEASE DO HIT A LIKE AND ACCEPT IT AS A SOLUTION IF IT RESOLVES FOR ERROR!

  • NimaiAhluwalia's avatar
    NimaiAhluwalia
    Continued Contributor
    5 years ago

    Hello Sujit_Thakur 

     

    Any help from the suggestion I provided ?

     

    IF MY SOLUTION HELPED YOU IN SOME OR THE OTHER WAY PLEASE DO HIT A LIKE AND ACCEPT IT AS A SOLUTION IF IT RESOLVES FOR ERROR!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Sujit_Thakur ,

     

    The issues you describe above are limitations in DirectQuery mode in Power BI Desktop. After we make changes in Query editor, we are not apply these changes to report.

     

    Please go to Applied Steps in Power Query to test step by step or as the message said, switch the mode to Import mode.

     

    I believe you can get more limitations  information from this official document:

    Using DirectQuery in Power BI - Power BI | Microsoft Docs

     

     

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

  • Sujit_Thakur's avatar
    Sujit_Thakur
    Solution Sage
    5 years ago

    Thanks all ,. for replying .
    After reading Power BI Documentation it was clear that like normal import mode it is not possible to work on data in power query editor , because as every time i move a slicer , PBI queries data on point from SQL Server.
    Hence what i did was i did the steps in python where i used to process data before taking it in power bi

    then it worked thanks all