Forum Discussion

SanthuWilly4's avatar
SanthuWilly4
Frequent Visitor
6 years ago

SQL Query in Direct Query Mode in Power BI

Hi , 

 

I'm trying to build a dynamic report using Direct Query connection connecting to SQL Server , I can able to connect to direct tables and from there I'm able to create visuals where the data is updating dynamically for every 30 seconds.

 

Here's my road block , most of the tables has to be transformed in data wise

For Example :

1. Few tables has delimited values in a single column and I need to split them into separate columns

2. Few tables has XML Column with XML content and I need to convert that XML content into individual columns 

 

But When I'm inserting SQL Query (by transforming all the columns and content) in Advance Section selecting Direct Query , it is throwing Syntax error ")" 

 

Please help , if I overcome this mostly I will be get going in creating the visuals.

 

Thank you 

Santosh Reddy

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    SanthuWilly4 - Going to be difficult with Direct Query doing that in Power Query. Probably have to do it in DAX and create columns pushing you into composite mode. Can you be specific with a use case? Alternatively use import mode. As a last resort maybe you can do certain things completely within measures. 

    • SanthuWilly4's avatar
      SanthuWilly4
      Frequent Visitor
      6 years ago

      Hi Greg , Thanks for your reply.

       

      Here's my sittuation I want to split SSRS report path /Folder1/Folder2/Folder3/Folder4/ReportName into it's individual columns.

      I able to do this in Import Mode by Column Split By Delimeter but this is feature is not supported in Direct Query mode.

      Also I have tried DAX function PATHITEM (which does almost same) then I came to know that most of the DAX functions are not available in Direct Query mode 😞

       

       

      Regards,

      Santosh Reddy

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        SanthuWilly4  Have you checked the box to allow unrestricted measures via DirectQuery and that I believe should allow to use most of the functions.