Adding Parameters in SQL Query & Passing Dynamically

References: https://learn.microsoft.com/en-us/shows/mvp-azure/pass-parameter-to-sql-queries-statement-using-power-bi

https://www.youtube.com/watch?app=desktop&v=3wvnjHPGNQQ&t=0s

Power BI Quick Tip: Using Dynamic M Query Parameters with SQL​​

Step-1 : Create Parameter in Power Bi

Create a Parameter in power Query Under Manage Parameter > New Parameter

Step-2 : Change values to parameter in where clause of the query

• Go to "Home" > "Transform Data" to open Power Query Editor.
• Select the table or query you want to modify.
• Click on the "Advanced Editor" and modify the query to include the parameters.

let

    // Convert the parameters to text
    P_YearText = Number.ToText(P_Year),
    P_SupplierCodeText = Text.From(P_SupplierCode),

    // Define the SQL query with the parameters
    Source = Sql.Database("dcazd365dtmigsql01.database.windows.net", "SET", [Query="SELECT * FROM [LSQD].[vwGsqaCoaDataDownload] WHERE [ShipmentYear] = '" & P_YearText & "' AND [SupplierCode] = '" & P_SupplierCodeText & "'"])
in
    Source

Step-3 : Bind the Parameter to the columns of the Table

• Go to Data Model ->Select the column you want to bind > Properties > Advanced > Bind to Parameter > Select the Parameter you want to bind