How to assign default values for selection parameters from a query

[Anonymous]

Hi Experts,

    I'd like to have power BI to refresh report data from a query based on a predefined criteria ,

    e.g, get the latest six months' data from a data source (query), with a scheduled job in power BI service.

    In case above, we need to select month values specifically to import data from a query 

    to form the dataset for working on reports and dashboards in power desktop,

    then publish them to power BI service cloud.

    The problem is that selecting the last six month values and

    publishing periodically seems not make any sense, not really a good idea.

    Therefore, is there any smart way to have this job done automatically ?

    In the "Advanced Editor" under query editor in power BI desktop, I can see the month values there like below

.....

    {Cube.ApplyParameter, "[!V000001]", {{"[0CALMONTH].[202402]", "[0CALMONTH].[202403]", "[0CALMONTH].[202404]", "[0CALMONTH].[202405]", "[0CALMONTH].[202406]", "[0CALMONTH].[202407]"}}},

   ....

     My idea is to have those values determined dynamically, e.g, those values would becomes [202403], [202404]....[202408]

     when the scheduled job runs in August. 

     Not sure whether it is possible or how to make the code changes to the codes in  "Advanced Editor"

     to make such a dynamic value determination happen.

    Any advice will be greatly apprecated.

[dk_dk]

Hi @Anonymous 

I am not a power query expert, but hopefully this solution works for you. I am sure someone can make the M code a bit more elegant:

My sample query with hardcoded values

let
    Source = Table.FromRows(
    {
        {202407},
        {202406},
        {202405},
        {202404},
        {202403},
        {202402}
    })

in
    Source

This just creates a table that contains the last 6 months in the format you want, hardcoded.

The modified query to return the same result dynamically:

let
    refTime = DateTime.Date(DateTime.LocalNow()),
    prevmonth5 = Date.AddMonths(refTime,-5),
    prevmonth4 = Date.AddMonths(refTime,-4),
    prevmonth3 = Date.AddMonths(refTime,-3),
    prevmonth2 = Date.AddMonths(refTime,-2),
    prevmonth1 = Date.AddMonths(refTime,-1),




    Source = Table.FromRows(
    {
        {Date.ToText(refTime,"yyyyMM")},
        {Date.ToText(prevmonth1,"yyyyMM")},
        {Date.ToText(prevmonth2,"yyyyMM")},
        {Date.ToText(prevmonth3,"yyyyMM")},
        {Date.ToText(prevmonth4,"yyyyMM")},
        {Date.ToText(prevmonth5,"yyyyMM")}
    })
in
    Source

I am not sure exactly how, but there should be a way to incorporate these variables (the refTime and prevmonths) in your query, in place of the hardcoded values. The query will calculate the current time with every refresh, so the months should change dynamically.


Hope this helps!

[dk_dk · Accepted solution]

I have moved your post to the Power Query section of the forum, hopefully someone here will be more knowledgeable and can provide you with a more elegant solution 🙂

[Anonymous]

Hi dk_dk,

    Thank you for your solution, it's really helpful.

    It works as I thought, the codes I made as below.

   ..............

   /* To define ym variables to get latest 6 months dynamically */
CurrDate = DateTime.Date(DateTime.LocalNow()),
prevmonth5 = Date.AddMonths(CurrDate,-5),
prevmonth4 = Date.AddMonths(CurrDate,-4),
prevmonth3 = Date.AddMonths(CurrDate,-3),
prevmonth2 = Date.AddMonths(CurrDate,-2),
prevmonth1 = Date.AddMonths(CurrDate,-1),
// Year yyyy
Y5 = Number.ToText(Date.Year(prevmonth5)),
Y4 = Number.ToText(Date.Year(prevmonth4)),
Y3 = Number.ToText(Date.Year(prevmonth3)),
Y2 = Number.ToText(Date.Year(prevmonth2)),
Y1 = Number.ToText(Date.Year(prevmonth1)),
Y0 = Number.ToText(Date.Year(CurrDate)),
// Month mm
M5 = Number.ToText(Date.Month(prevmonth5),"00"),
M4 = Number.ToText(Date.Month(prevmonth4),"00"),
M3 = Number.ToText(Date.Month(prevmonth3),"00"),
M2 = Number.ToText(Date.Month(prevmonth2),"00"),
M1 = Number.ToText(Date.Month(prevmonth1),"00"),
M0 = Number.ToText(Date.Month(CurrDate),"00"),
// YearMomth - yyyymm
YM5 = Text.Combine({"[0CALMONTH].[", Y5, M5, "]"}),
YM4 = Text.Combine({"[0CALMONTH].[", Y4, M4, "]"}),
YM3 = Text.Combine({"[0CALMONTH].[", Y3, M3, "]"}),
YM2 = Text.Combine({"[0CALMONTH].[", Y2, M2, "]"}),
YM1 = Text.Combine({"[0CALMONTH].[", Y1, M1, "]"}),
YM0 = Text.Combine({"[0CALMONTH].[", Y0, M0, "]"}),
#"QM_C03/ZQ_QM_C03_001X" = QM_C03{[Id="QM_C03/ZQ_QM_C03_001X"]}[Data],
#"Added Items" = Cube.Transform(#"QM_C03/ZQ_QM_C03_001X",
{
// {Cube.ApplyParameter, "[!V000001]", {{"[0CALMONTH].[202402]", "[0CALMONTH].[202403]", "[0CALMONTH].[202404]", "[0CALMONTH].[202405]", "[0CALMONTH].[202406]", " [0CALMONTH].[202407]"}}},
{Cube.ApplyParameter, "[!V000001]", {{YM5, YM4, YM3, YM2, YM1, YM0}}},

...........................