CODEWITHSUNDEEP
powerbipowerqueryevaluationm
Denis
Denis

Reputation: 761

How Expression.Evaluate works?

How to parameterize The first day of Month in Power Query?

Tried this way (Expression.Evaluate + Day.Monday), but gives the error:

let
    txtFirstDayOfMonth = "Monday", // this parameter comes from Excel/Source file
    numFirstDayOfMonth = Expression.Evaluate("Day."&txtFirstDayOfMonth)        
in
    numFirstDayOfMonth

Estimated output:

numFirstDayOfMonth =  1   // numFirstDayOfMonth = Day.Monday

Real output:

    Expression.Error: [1,1-1,11] The name 'Day.Monday' doesn't exist in the current context.
        Details:
        [List]

P.S. Want to avoid hack with switch statement.

THE ANSWER (Summary):

Expression.Evaluate("Day."&txtFirstDayOfMonth,#shared)

please see details in this post by Ron Rosenfeld

However, as Jeroen Mostert mentions in comments, probably it's better to use switch or else if construction for this purpose(as far as there is no switch statement in power query). Something like:

...
#"Added Conditional numFirstDayOfWeek" = Table.AddColumn
(#"Added Custom", "numFirstDayOfWeek", each 
     if Text.Contains(Text.Lower([FirstDayOfWeek]), "su") then 0 
else if Text.Contains(Text.Lower([FirstDayOfWeek]), "mo") then 1 
else if Text.Contains(Text.Lower([FirstDayOfWeek]), "tu") then 2 
else if Text.Contains(Text.Lower([FirstDayOfWeek]), "we") then 3 
else if Text.Contains(Text.Lower([FirstDayOfWeek]), "th") then 4 
else if Text.Contains(Text.Lower([FirstDayOfWeek]), "fr") then 5 
else if Text.Contains(Text.Lower([FirstDayOfWeek]), "sa") then 6 
else 1
)

Upvotes: 1

Views: 1199

Answers (2)

Rick de Groot - gorilla.bi
Rick de Groot - gorilla.bi

Reputation: 406

If you really want to go for the Expression.Evaluate way (I'm not sure I would) here's some thoughts.

Youcan specify your identifiers in two ways. Either you do it manually with a record:

[Day.Monday = Day.Monday, Day.Tuesday = Day.Tuesday ]

Or you can make use of the #shared global identifier.

For example:

Expression.Evaluate(
  "List.Sum( { 1, 2, 3 } )", 
  [List.Sum = List.Sum] 
)

Returns the same as:

Expression.Evaluate(
  "List.Sum( { 1, 2, 3 } )", 
  #shared 
)

More examples on this you can find here: https://powerquery.how/expression-evaluate/

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Your nomenclature with regard to first day of month is confusing me, but to return your expected value from Expression.Evaluate you need to define the environment. As written, Expression.Evaluate does not understand Day.. So we add a record of the global enviroment using #shared keyword (which includes the record fields cited by @JeroenMostert, but seems simpler to add to the function).

Expression.Evaluate("Day."&txtFirstDayOfMonth,#shared)

will return the weekday number of txtFirstDayOfMonth where that is a weekday name.

There are a number of blogs going into more detail about the environment variable. See this by Chris Webb for more enlightenment.

If I were doing this, however, I'd probably just make a List of the weekdays and use the List.PositionOf function to return the number. And also add some code to avoid issues with typos, capitalization, etc -- maybe just look at the first three letter.

Upvotes: 2

Related Questions