Forum Discussion
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", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}, DefaultValue="Sunday", Type="Text", IsParameterQueryRequired=true]I want to pass this parameter through my M language, to replace "Day.Sunday" with the value from the parameter in the step below:
= Table.AddColumn(#"Inserted Day of Week ID", "Start of Week", each Date.StartOfWeek([Dates],Day.Sunday), type date)
That way a user can determine the start day of the week using the parameter.
Can anyone help with this?
Thanks,
Mark
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.
5 Replies
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.
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
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.