Power BI: Dynamic SQL parameters question

Hi OndrejVyhnal ,

In Power BI using DirectQuery mode, you cannot dynamically inject parameters into a native SQL query by concatenating strings inside the M code as you attempted. The syntax AND A_MONTH_ID = " & A_MONTH_ID_PARAM & "" will not work because Power Query doesn't support that kind of inline variable expansion in a SQL string. Instead, you need to define a parameter in Power BI (via Manage Parameters), and reference it through the native query interface—not by M code concatenation.

Create a numeric parameter called A_MONTH_ID_PARAM in Power BI. Then when connecting to Oracle, instead of writing M code manually, use the built-in connector and paste the query below directly in the SQL dialog:

SELECT
    ISIN,
    ISSUER_NAME,
    ISIN_ORDER,
    PRD_DATE,
    CORE_REPORTING_STRUCTURE_ID,
    EXCHANGE_NAME,
    EXCHANGE_CODE,
    A_MONTH_ID,
    STOCK_REPORT_ID,
    WJXBFS1 As ADJ_MIXED_PRICE,
    WJXBFS1 as PERF
FROM XSR_FE.PEER_GROUP_COMPARISON_POWERBI_POC
WHERE WJXBFS1 IS NOT NULL
  AND A_MONTH_ID = ?

When you enter this query and press OK, Power BI will detect the question mark ? as a placeholder and prompt you to bind it to a parameter. Select the A_MONTH_ID_PARAM you created. This allows you to drive the Oracle query dynamically via parameter value changes from the Power BI UI. Keep in mind, in DirectQuery mode, these parameter changes cannot be driven directly by slicers or visuals unless you use paginated reports or workarounds with query reduction settings.

Best regards,