Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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"

General Comment
Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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)),

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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)),
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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