Search code examples
powerbipowerqueryevaluationm

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
)

Solution

  • 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.