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

Issue with Filtering Data using IF ELSE Condition In Power Query

Hi Mates,

 

I am facing issue while filtering the data based on specific If Else Condition. So generally I have a data that I want to filter based on Current Day Name. If Current Day is 'Monday' then I want to load last 3 days data based on my Date Column, and if Current Day is in 'Tuesday To Friday' then I want to load only prior day's data. I have a code that is skipping then condition and only loading Prior Day data even if the condition is true. To test the code I have changed the Current Date to check as 'Thursday'. Please help me if I am doing something wrong in code. Thank in advance.

 

Below is the M Query Code and the Red colored code is the culprit one,

 

let
Source = Sql.Database("VMCEDW01", "DATAMART_OPERATIONS"),
FieldSales_vw_CountSheetHistory = Source{[Schema="FieldSales",Item="vw_CountSheetHistory"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(FieldSales_vw_CountSheetHistory,{{"Created On", type date},{"Current Date", type date}}),

#"Filtered Rows" = if {{"Current Date", each Date.DayOfWeekName(_), type text}} = "Thursday"
then Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Created On], 3))
else Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Created On], 1)),

#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Location"}, {{"PD Count", each Table.RowCount(_), Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Location", "PD Stores"}})
in
#"Renamed Columns"

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Try this for your red part instead:

CurrentDate = Date.From(DateTime.LocalNow()),
DaysToLoad = if Date.DayOfWeekName(CurrentDate) = "Monday" then 3 else 1,
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Created On], DaysToLoad)),

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Try this for your red part instead:

CurrentDate = Date.From(DateTime.LocalNow()),
DaysToLoad = if Date.DayOfWeekName(CurrentDate) = "Monday" then 3 else 1,
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Created On], DaysToLoad)),

Thank you @AlexisOlson , It works perfectly as I am expecting..!!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors