cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Macaurly Frequent Visitor
Frequent Visitor

Power Query choose a step to create based on a condition

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. 

 

3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Power Query choose a step to create based on a condition

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Macaurly Frequent Visitor
Frequent Visitor

Re: Power Query choose a step to create based on a condition

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. 

 

 

Moderator v-yuezhe-msft
Moderator

Re: Power Query choose a step to create based on a condition

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.