Dynamic query from SQL procedure

[davidz106]

First part:

I have a query (SQL procedure)

Source= Sql.Database("server", "cataloge_item", [Query="EXEC procOderBySampleID @ID = '5134'"])

Instead of manually inputing 4-digit value (i.e. 5134) I want to write M code to retrive this value from other query (Query A, column 3). 

I solved that in VBA as:

ID1 = Worksheets("1").Cells(i, 3)
query = "procOderBySampleID @ID = '" & ID1 & "'"

I am new to M and don't have a clue how to tackle this.

Second part: 

Since Query A contains 100s of values I would like to make the query B dynamic in the sense that only selected/filtered data in power BI report (values of query A) are queried in B. Is this possible?

And to complicate things further, I would like to limit the query B to 5 results. This is mainly because I do not want to overload SQL proc.

Any help greatly appreciated!

[Anonymous · Accepted solution]

Hi @davidz106 ,

I think you can try to load data from SQL Server with specific ID based on the filter of parameters.

Firstly, create a list of parameters with the ID you need.

The whold M code.

let
    Source = Sql.Database("Server", "Database"),
    dbo_Sample = 
        let _ID = ID
        in
        Value.NativeQuery(
            Source,
            "SELECT * FROM Sample
            WHERE ID=@ID",
            [ID = _ID])
in
    dbo_Sample

Result is as below. ID =5134, we can load all data in ID =5134 to Power BI.

For more details: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI

Best Regards,
Rico Zhou

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