Dynamic M query parameters in Power BI Desktop

[Anonymous]

Dynamic M Parameters is not working.we have followed the below process:

1.  We have created a Date parameter

2. We have imported a table using SQL query where date variable is declared as 12/04/2022.

3. Now, In Advanced Editor we are changing the code to get the payment created date as per the declare date variable.

let

Filterquery = "SELECT#(lf)SP.[PaymentRef]#(lf), SP.[ReportingType]#(lf), SP.[PaymentMethod]#(lf), SP.[PaymentCreatedDate]#(lf), SP.[BillAllocationStatus]#(lf), SP.[BillAmountUnallocated]#(lf), 'Allocated In' as [Status]#(lf)FROM [DW].[F_Drv_Suspense_Payments] SP#(lf) where SP.[PaymentCreatedDate] = " & DateTime.ToText (P_Date, "yyyy-mm-dd"),

Source = Sql.Database("wtr-uks-tst-datafactorydb.database.windows.net", "WTR-UKS-TEST-DWHDB", [Query="SELECT#(lf)SP.[PaymentRef]#(lf), SP.[ReportingType]#(lf), SP.[PaymentMethod]#(lf), SP.[PaymentCreatedDate]#(lf), SP.[BillAllocationStatus]#(lf), SP.[BillAmountUnallocated]#(lf), 'Allocated In' as [Status]#(lf)FROM [DW].[F_Drv_Suspense_Payments] SP#(lf)"])

in
Source

As per the above process; The Payment created date supposed to filtered as per the declared Date parameter which is "12/04/2022".

Conclusion; We want to create a "Dynamic date parameter" so that user should be able to change the date by itself to filter the data.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can update your M query codes as below in Advanced Editor, the part with red font is updated ones...

let
Source = Sql.Database("wtr-uks-tst-datafactorydb.database.windows.net", "WTR-UKS-TEST-DWHDB",
[Query="SELECT#(lf)SP.[PaymentRef]#(lf), SP.[ReportingType]#(lf), SP.[PaymentMethod]#(lf), SP.[PaymentCreatedDate]#(lf), SP.[BillAllocationStatus]#(lf), SP.[BillAmountUnallocated]#(lf), 'Allocated In' as [Status]#(lf)
FROM [DW].[F_Drv_Suspense_Payments] SP#(lf) where format(SP.[PaymentCreatedDate],'M/d/yyyy') = '"&Date.ToText(P_Date)&"' "])
in
Source

Power BI Introduction: Working with SQL Server data in Power BI Desktop

Power BI Desktop Query Parameters

Best Regards