How do I build report against sql query against SQL Server database and Pass parameters to query

Hi All,
Firstly,  vijaycp2  and parry2k thank you for yours solutions!
And vijaycp2 ,Based on the error code you uploaded, it appears to be related to placeholders in SQL queries. This is because when using parameters in a SQL query, you can't simply concatenate strings directly with & as you can in some programming languages.
You can use a combination of & and Parameter to refer to parameters in Power BI:

let
    Source = Sql.Databases("VM"),
    AdventureWorks2022 = Source{[Name="AdventureWorks2022"]}[Data],
    Sales_Store = AdventureWorks2022{[Schema="Sales",Item="Store"]}[Data],
    BusinessEntityIDValue = Value,  
    FilteredSalesStore = Sql.Database("VM", "AdventureWorks2022", 
                                       [Query="SELECT * FROM Sales.Store WHERE BusinessEntityID = " & Text.From(BusinessEntityIDValue)]),
    FinalResult = FilteredSalesStore
in
    FinalResult

If you need to use the Value.NativeQuery function then you can try the following code, hope it helps:

let

    Source = Sql.Databases("VM"),
    Query = "SELECT * FROM Sales.Store WHERE BusinessEntityID = @BusinessEntityID",
    FilteredSalesStore = Value.NativeQuery(
        Source{[Name="AdventureWorks2022"]}[Data], 
        Query, 
        [BusinessEntityID = Value]  
    ),
    
    FinalResult = FilteredSalesStore
in
    FinalResult

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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