Power Query choose a step to create based on a condition

[Macaurly]

Hello, 

I have managed to filter my table using Table.SelectRows so that I only show rows where a Date field lies between two dates for payroll purposes. So as a contextual example, I want to see all rows between the 11th of March 2018 inclusive and the 10th of April 2018 exclusive. This is my current formula:

= Table.SelectRows(Source, each ([EntryDate] >= #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)), 11) and [EntryDate] < #date(2018, Date.Month(DateTime.LocalNow()), 11)))

This works but I have noticed that if I refresh the data on the 28th of March, I still see data where the Date field is between the 11th of February and the 10th of March. This is because I am using Date.AddMonths(<today>, -1) which means the correctly filtered results for this payroll period won't be displayed until we enter April.

My question is, can I use the advanced editor to put in a condition which dictates which step I create?

So if today's date is after the 11th of the month I use this step:

Table.SelectRows(Source, each ([EntryDate] >= #date(2018, Date.Month(DateTime.LocalNow()), 11) and [EntryDate] < #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), 1), 11)))

But if today's date is before the 11th of the month I use this step: 

Table.SelectRows(Source, each ([EntryDate] >= #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)), 11) and [EntryDate] < #date(2018, Date.Month(DateTime.LocalNow()), 11)))

I have tried to get this to work using 'if then else' within the advanced editor but I suspect my syntax is incorrect.

let
    Source = #"Timesheets SQL",
    #"Filtered between 11th last month to 10th this month" = 
    if Date.Day(DateTime.LocalNow()) < 11
        then Table.SelectRows(Source, each ([EntryDate] >= #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)), 11) and [EntryDate] < #date(2018, Date.Month(DateTime.LocalNow()), 11)))
        else Table.SelectRows(Source, each ([EntryDte] >= #date(2018, Date.Month(DateTime.LocalNow()), 11) and [EntryDate] < #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), 1), 11))),
    #"Grouped Rows" = Table.Group(#"Filtered between 11th last month to 10th this month", {"SharepointHeaderID", "Employee_ID", "Selection_1_Value", "Selection_2_Value"}, {{"Weekly Hours", each List.Sum([Hours]), type number}})
in
    #"Grouped Rows"

The above gives the error 'Token Comma expected' but I can't seem to insert one in the right place to make it work!

Thanks in advance,

Macaurly. 

[JOSELUISMTZRMZ1]

Hello

I had the same need so here what I could do, the thing is that you have to return a table to continue in the step you are setting the If, I could figure out that:   THEN let steps in result ELSE let steps in result ... in #step     is how to

let

your steps

NAME_OF_STEP_WHERE_IF_NEEDED = if CONDITION 

THEN

   let 

   all your steps code  for true as usual

   in  #END

  ELSE

  let 

    all your steps code  for false as usual

  in  #END

here your main steps path continues or ends

in NAME_OF_STEP_WHERE_IF_NEEDED

[v-yuezhe-msft]

@Macaurly,

Change your code to the following and check if you get expected result.

let
    Source = #"Timesheets SQL",

    #"Filtered1" = Table.SelectRows(Source, each ([EntryDate] >= #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)), 11) and [EntryDate] < #date(2018, Date.Month(DateTime.LocalNow()), 11))),
    #"Grouped Rows1" = Table.Group(#"Filtered1", {"SharepointHeaderID", "Employee_ID", "Selection_1_Value", "Selection_2_Value"}, {{"Weekly Hours", each List.Sum([Hours]), type number}}),

  #"Filtered2"= Table.SelectRows(Source, each ([EntryDte] >= #date(2018, Date.Month(DateTime.LocalNow()), 11) and [EntryDate] < #date(2018, Date.Month(Date.AddMonths(DateTime.LocalNow(), 1), 11))),
  #"Grouped Rows2" = Table.Group(#"Filtered2", {"SharepointHeaderID", "Employee_ID", "Selection_1_Value", "Selection_2_Value"}, {{"Weekly Hours", each List.Sum([Hours]), type number}}),

result = 
        if Date.Day(DateTime.LocalNow()) < 11
        then #"Grouped Rows1" 
        else #"Grouped Rows2"

in
    #"result"

Reference:
https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then...


Regards,
Lydia

[Macaurly]

Hi @v-yuezhe-msft Lydia, 

Thank you for offering a solution.

I have tried the code you suggested but I still get a 'Token Literal expected' warning which highlights the word 'if'. 

Thanks, 

Macaurly. 

[v-yuezhe-msft]

@Macaurly,

I would recommend you re-check the code in the PBIX sample file of the above blog, and then change your code.

If you still get error, please help to post screenshot about  your code, and share your PBIX file to me if it is possible. You can share PBIX file via Private Message.

Regards,
Lydia