Using a list query as parameter file for dynamic table filtering

[Anonymous]

I need help with being able to use a list query value (reportEndDate) as a parameter (DataPullEnddate) to filter my Dimdate as shown in the M query below.

I observed that when the value of reportEnddate changes, parameter DataPullEndDate which derives its value from ReportEnddate remains static 9/30/2020 even after refresh.

I want the new date to take effect in PowerBI service automatically each time reportEndDate changes after refresh. 

See screenshots attached.      https://1drv.ms/b/s!AlMdRxAveLesgbQN1BnQwMjUIa97JA

let

    Source = Sql.Database("ebhreportingdev-dw.database.windows.net", "ebhreportingdw"),

    dw_dimDate = Source{[Schema="dw",Item="dimDate"]}[Data],

    #"Renamed Columns" = Table.RenameColumns(dw_dimDate,{{"calDate", "Calendar date"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "calDayNo", each Date.Day([Calendar date])),

    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"dimDateKey", "Calendar date", "calMonthNo", "calDayNo", "calMonth", "calQuarter", "calYear", "currentCalDay", "currentCalMonth", "currentCalYear", "currentCalMTD", "currentCalYTD", "recordEffectiveFromDate"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Calendar date", "Date:"}}),

    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each [#"Date:"] >= DataPullStartDate and [#"Date:"] <= DataPullEndDate)

in

    #"Filtered Rows1"

Link:

https://1drv.ms/b/s!AlMdRxAveLesgbQN1BnQwMjUIa97JA

[lbendlin]

" parameter DataPullEndDate which derives its value from ReportEnddate"

How?  You can overwrite parameters at runtime, but you cannot store that change in Power Query. Power BI has no memory of its own activities. Anything memory related has to happen outside of Power BI, for example with a second query where you fetch that ReportEndDate from the data source.

You can simplify your query a bit:

let
    Source = Sql.Database("ebhreportingdev-dw.database.windows.net", "ebhreportingdw"),
    dw_dimDate = Source{[Schema="dw",Item="dimDate"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(dw_dimDate,{{"calDate", "Date:"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "calDayNo", each Date.Day([#"Date:"])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [#"Date:"] >= DataPullStartDate and [#"Date:"] <= DataPullEndDate)
in
    #"Filtered Rows1"

[Anonymous]

@lbendlin 

Thank you for your response.   A quick solution to the issue would be answered as below:

In this line:

#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [#"Date:"] >= DataPullStartDate and [#"Date:"] <= DataPullEndDate)

What M query syntax will allow me replace param DataPullStartdate  with list value ReportstartDate and param DatapullEndDate with list value ReportEndDate?  In that case I wont need the param files after replacing with the list values.  

Those list values are from a backend table.  

Using paran files does not solve my proble as the list values do not autiomatically populate the param files. I intend to apply those values to dynamically  filter my DimDate without having to change the param values in Desktop.

Thank you for your usual quick response.

 

[lbendlin]

Not sure why you want to use lists for single values, but in that case you need to say List.First() to get to the value