Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power Query - Pass Parameter through M Language to amend StartOfWeek Day

Hi,   I would like to dynamically control the Start of Week field   I've created a text parameter that lists each Day of the Week: "Sunday" meta [IsParameterQuery=true, List={"Monday", "Tuesday...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

    You can try the following custom column.

     =let 
_week=if Parameter1="Sunday" then 0 
else if Parameter1="Monday" then 1 
else if Parameter1="Tuesday" then 2
else if Parameter1="Wednesday" then 3 
else if Parameter1="Thursday" then 4
else if Parameter1="Friday" then 5
else if Parameter1="Saturday" then 6 else null
in Date.StartOfWeek([Dates],_week))

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

The function of the Data.StartOfWeek(), the paramater need to be a number instead of a text.

You need to change your paramater list to a number list from 0 to 6.

 

1 meta [IsParameterQuery=true, List={0, 1, 2, 3, 4, 5, 6}, DefaultValue=0, Type="Number", IsParameterQueryRequired=true]

 

 

Then change the funciton to the following .

 

Table.AddColumn(#"Inserted Day of Week ID", "Start of Week",  each Date.StartOfWeek([Dates],Parameter1), type date)

 

It can work, and you can refer to the attachment.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    I'm trying to make it so users can do this quickly, which is why I'm having Text in the parameter as this will make more sense to users than having a number. If could get confusing if they don't know that 0 = Sunday.

     

    Is there another way around this? Could the parameter be used to amend the text at the end of "Day.Sunday"?

     

    Thanks,

    Mark

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 

      You can try the following custom column.

       =let 
_week=if Parameter1="Sunday" then 0 
else if Parameter1="Monday" then 1 
else if Parameter1="Tuesday" then 2
else if Parameter1="Wednesday" then 3 
else if Parameter1="Thursday" then 4
else if Parameter1="Friday" then 5
else if Parameter1="Saturday" then 6 else null
in Date.StartOfWeek([Dates],_week))

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

         

        It's not quite working. I'm getting the following error:

        Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

        Could you update your PBIX file and with your suggestion?

         

        Thanks,

        Mark