Unable to make any changes in Direct Query mode after loading data using a SQL query

[hyder]

Hello Everyone

I am connecting to a SQL Server database using direct query mode. I used the following SQL script to laod the data directly. No after I loaded my data I am unable to perform any replacement or any other changes in the power query mode. Power Bi gives a message that this step can't be performed in Direct Query mode and I should shift to Import Mode, which I wish to avoid due to large data size and also I need the show live data. Now is there any way around to this?

select md2.MRNo, md2.DocNo, md2.SLNO, md.AccountNo,md.DepositDate, ms.BranchCode, ms.DeptCode, ms.ClassCode, (ms.CurrencyRate * md2.DepositNet) as Premium
from MRStatus_Details2 md2
inner join MRStatus_Details md on md2.mrno=md.MRNo and md2.slno=md.SLNO
inner join MRStatus ms on ms.mrno=md.MRNo

Where md.AccountNo not in ('V1234', 'V 1234')
and ms.BranchCode not in ('AHO')
and ms.DeptCode not in ('SUN', 'SUNDRY', 'DEP', 'DEPOSIT')
and md.DepositDate between '01-jan-2019' and '31-dec-2020'

The M Query comes to this: 

let
Source = Sql.Database("192.168.51.130", "InsData1", [Query="select md2.MRNo, md2.DocNo, md2.SLNO, md.AccountNo,md.DepositDate, ms.BranchCode, ms.DeptCode, ms.ClassCode, (ms.CurrencyRate * md2.DepositNet) as Premium#(lf)from MRStatus_Details2 md2#(lf)inner join MRStatus_Details md on md2.mrno=md.MRNo and md2.slno=md.SLNO#(lf)inner join MRStatus ms on ms.mrno=md.MRNo #(lf)#(lf)Where md.AccountNo not in ('V1234', 'V 1234')#(lf)and ms.BranchCode not in ('AHO')#(lf)and ms.DeptCode not in ('SUN', 'SUNDRY', 'DEP', 'DEPOSIT')#(lf)and md.DepositDate between '01-jan-2019' and '31-dec-2020'"])
in
Source

If I add ny steps like rpelacement or add a column after this, Power BI doesn't let me do that unless I shift to Import mode, but I must use Direct Query mode. What will be the best approach?

@Seth_C_Bauer 

@Greg_Deckler 

@v-qiuyu-msft 

[v-yingjl]

Hi @hyder ,

Sorry for replying late. Similar with the data moel limitaions using DAX in direct query, M query functions also have limitations that you can only use the query codes to create columns only on the current underlying dataset instead of producing native queries to the data source.

Please refer:

1. Power BI Native Query and Query Folding 
2. About using DirectQuery in Power BI 
3. Query folding guidance in Power BI Desktop 

In this case, if the query is about aggregation, you can try to use DAX to create measures; if the query is common about columns, you can create calculated columns.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.