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

[Mark_Holland_GD]

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

[Mark_Holland_GD]

@Anonymous  I understand now, you've added this into the Custom Column box. It's working now.

Thanks,

Mark

[Anonymous]

Hi @Mark_Holland_GD 

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.

[Mark_Holland_GD]

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 · Accepted solution]

Hi @Mark_Holland_GD 

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.

[Mark_Holland_GD]

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