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 

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