Power Query 5 days ago rule

[Ocasio27]

In the conditional column, I want to create a rule that, if a column value is less than 5 days ago, then the value should be "pending"

[#2]

@Ocasio27 

Insert first in the wizard any date you wish. In formula bar it will be something like

= Table.AddColumn(#"Changed Type", "Custom", each if [A] > #date(2020, 7, 2) then "pending" else null)

and be sure formula works with your data. After in formula bar change #date(...) on Date.From(Date.AddDays(DateTime.LocalNow(),-5))

= Table.AddColumn(#"Changed Type", "Custom", each if [A] > Date.From(Date.AddDays(DateTime.LocalNow(),-5)) then "pending" else null)

[#3]

@Sergei Baklan 

It works but I explained it incorrectly and is not what I was expecting.

I already have a conditional column with rules, if else if else, I can choose a column with dates and add after a specific date, but I can only input a fixed date, for example june 2 which is 5 days ago, however that query will change often and I need it to apply this rule for the last 5 days no matter when do I update it.

Maybe the only way to make this work is with a custom column rather than a conditional column, but the conditional column is just easier to work with.

[#4 · Accepted solution]

@Ocasio27 

Conditional column is only subset of custom columns with user interface to generate if ... then ... else

Anyway, you already have some statement with fixed date. Stay on step where this formula was generated (or use Advanced editor) and change concrete date which is in form #date(2020,7,2) or so, on

Date.From(Date.AddDays(DateTime.LocalNow(),-5))

[#5]

@Sergei Baklan 

I am trying to add this as a DAX step

Note that all rules with "Approved" have priority.

= SWITCH (

    TRUE (),

    'Test'[Number] = "X001", "Approved",

    'Test'[Number] = "X002", "Approved",

    'Test'[Number] = "X003", "Approved",

    'Test'[Number] = "X004", "Approved",

    'Test'[Number] = "X005", "Approved",

    'Test'[Number] = "X006", "Approved",

    'Test'[Number] = "X007", "Approved",

    'Test'[Number] = "X008", "Approved",

    'Test'[Number] = "X009", "Approved",

    'Test'[Number] = "X010", "Approved",

    "Denied"

)

[#6]

@Ocasio27 

I'm not sure what do you mean under "DAX step"

[#7]

@Sergei Baklan 

That same function but in DAX language to use in Power Pivot

[#8]

@Ocasio27 

Yes, but where? - you add calculated column or measure or what?

[#9]

@Sergei Baklan 

I decided to do the same but in Power Pivot since the data comes from an .atomsvc

I already added a column with the following code, but I want to add a line that, if the column [Date] has a day that is within the last 5 days, then it adds pending. Here is the code without that line.

New column is called [Result]

Column [Number] Is used to define if its approved or not, this one has priority no matter the others

Column [Date] Is used to define as "Pending" if its within the last 5 days

Else, it should be "Denied"

[Result]= SWITCH (
TRUE (),
'Test'[Number] = "X001", "Approved",
'Test'[Number] = "X002", "Approved",
'Test'[Number] = "X003", "Approved",
'Test'[Number] = "X004", "Approved",
'Test'[Number] = "X005", "Approved",
'Test'[Number] = "X006", "Approved",
'Test'[Number] = "X007", "Approved",
'Test'[Number] = "X008", "Approved",
'Test'[Number] = "X009", "Approved",
'Test'[Number] = "X010", "Approved",
"Denied"
)

My intention is that, unless is one of those number the result should be "Denied" with the exception of those with a number in the list I am adding, those should be "Approved" no matter if they have a date within the last 5 days.

[#10]

@Ocasio27 

Perhaps

=IF('Test'[Number] IN {"X001","X002","X003","X004","X005","X006","X007","X008","X009","X010"},
      "Approved",
       IF(TODAY()-'Test'[Date]<=5,"Pending","Denied"))

[#11]

@Sergei Baklan 

Amazing, thank you.

[#12]

@Ocasio27 , you are welcome

[#13]

@Sergei Baklan 

Sorry for the late reply but, how do you do that nice syntax? I use Visual studio code and is too plain simple.

Is there any "assistant" app to develop scripts in .m?

[#14]

@Ocasio27 

Above concrete code is just in formula bar in Power Pivot, like Excel formulas in it's formula bar. Additionally I use DAX Studio , but that's not the code generator.