Stored procedures with parameters in a Direct Query connection

[SagarB]

How to get data from a SQL-server by using stored procedures with parameters in a Direct Query connection?

Facing Error (Microsoft SQL: Incorrect syntax near the keyword 'EXEC') 
I've tried using Power Query's advanced editor and creating new parameters still facing the same error, but I'm wondering if there are any other methods I could try.

[VijayP · Accepted solution]

@SagarB 

CREATE VIEW MyView AS
SELECT * FROM MyTable WHERE Column1 = 'Value1';

or use Query Folding by adding extra steps in using M Language

#"Filtered Rows" = Table.SelectRows(Sales, each [SaleDate] >= #date(2023, 1, 1) and [SaleDate] <= #date(2023, 12, 31))

[VijayP]

@SagarB  Oh! I missed that , DirectQuery mode in Power BI does not support the execution of stored procedures directly. Instead, create a view or a table-valued function in the database and query this object in Power BI

[SagarB]

@VijayP  Thank you. I have one question: for view can we pass parameters?

[SagarB]

@VijayP  "I reviewed the thread you mentioned, and it clearly states that this solution will only work for Import Mode. However, I am using Direct Query mode."

[VijayP]

@SagarB 

Please use this thread - https://community.fabric.microsoft.com/t5/Power-Query/Microsoft-SQL-Incorrect-syntax-near-the-keywor...