Forum Discussion
Using SQL declared variables into Power BI
Thankyou, MohamedFowzan1, CPCARDOSO, kushanNa for your responses.
Hi cheid_4838,Based on my understanding, in Import mode parameters can only be applied during refresh and cannot be bound to slicers. Consequently, the Bind to Parameter option does not appear. In DirectQuery mode, dynamic M parameters are supported; however, the current SQL query contains unsupported constructs such as correlated subqueries and ORDER BY in subqueries. These constructs break query folding, preventing DirectQuery from executing the query in its present form.
If you remain in Import mode, parameters will work only at refresh time and will not be dynamic. If you require slicer driven parameters, switch to DirectQuery and simplify the SQL by using a database view or stored procedure that accepts parameters. This will enable query folding and allow binding slicers to parameters.
Additionally, please refer to the following links:
Value.NativeQuery - PowerQuery M | Microsoft Learn
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft LearnWe hope this information helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
Hi cheid_4838
Looks like the error is due to trying concate with a text and date.
Try this:
let
// Define Start and End dates as Power Query parameters or fixed values
StartDate = Date.ToText(#date(2024, 8, 4), "MM/dd/yyyy"),
EndDate = Date.ToText(#date(2024, 8, 10), "MM/dd/yyyy"),
// Construct the full SQL query string with declared variables
SqlQuery =
"DECLARE @StartDate AS DATETIME; " &
"DECLARE @EndDate AS DATETIME; " &
"SET @StartDate = '" & StartDate & "'; " &
"SET @EndDate = '" & EndDate & "'; " &
"SELECT " &
"ih.ord_hdrnumber as OrderNum, " &
"CAST(ih.ivh_deliverydate as Date) as Delivery, " &
"orig.cty_name as Origin, " &
"orig.cty_state as OSt, " &
"orig.cty_region2 as OReg, " &
"ISNULL(CASE WHEN l.lgh_split_flag = 'N' THEN dest.cty_name ELSE legdest.cty_name END,'Temple') as Dest, " &
"ISNULL(CASE WHEN l.lgh_split_flag = 'N' THEN dest.cty_state ELSE legdest.cty_state END, 'TX') as DSt, " &
"ISNULL(CASE WHEN l.lgh_split_flag = 'N' THEN dest.cty_region2 ELSE legdest.cty_region2 END, 'TX-TEM') as DReg, " &
"l.lgh_split_flag as SplitFlag, " &
"CASE WHEN ISNULL((SELECT SUM(ISNULL(stp_lgh_mileage, 0)) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus IN ('MT','BT')),0) > 200 " &
"THEN 200 ELSE ISNULL((SELECT SUM(ISNULL(stp_lgh_mileage, 0)) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus IN ('MT','BT')),0) END as MTMilesOG, " &
"/* Add remaining columns here as in your full query, properly concatenated with & and enclosed in quotes */ " &
"ih.ivh_totalcharge as BHGrossAmt, " &
"(SELECT MAX(stp_mfh_sequence) FROM stops s (NOLOCK) WHERE s.mov_number = ih.mov_number) as LastStopID " &
"FROM invoiceheader ih (NOLOCK) " &
"INNER JOIN city orig (NOLOCK) ON orig.cty_code = ih.ivh_origincity " &
"INNER JOIN city dest (NOLOCK) ON dest.cty_code = ih.ivh_destcity " &
"LEFT JOIN legheader l (NOLOCK) ON l.ord_hdrnumber = ih.ord_hdrnumber " &
"LEFT JOIN city legdest (NOLOCK) ON legdest.cty_code = l.lgh_endcity " &
"LEFT JOIN stops s (NOLOCK) ON s.mov_number = ih.mov_number AND s.stp_mfh_sequence = (SELECT MAX(stp_mfh_sequence) FROM stops s (NOLOCK) WHERE s.mov_number = ih.mov_number) " &
"WHERE ih.ivh_revtype3 = 'SC-BH' " &
"AND ih.ivh_billto NOT LIKE 'STATEM%' " &
"AND ih.ivh_deliverydate >= @StartDate " &
"AND ih.ivh_deliverydate < DATEADD(day, 1, @EndDate) " &
"AND (l.lgh_split_flag IN ('N','S') OR l.lgh_split_flag IS NULL) " &
"AND ih.ivh_invoicenumber = (SELECT MAX(ih2.ivh_invoicenumber) FROM invoiceheader ih2 (NOLOCK) WHERE ih2.ord_hdrnumber = ih.ord_hdrnumber AND RIGHT(ivh_invoicenumber, 1) <> 'B') " &
"ORDER BY MTMilesOG DESC"
,
// Connect to SQL Server Database (change Server and Database names)
Source = Sql.Database("your_server_name", "your_database_name", [Query=SqlQuery])
in
Source