Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
@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
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |