Forum Discussion

smjzahid's avatar
smjzahid
Icon for Helper V rankHelper V
6 years ago
Solved

How to use Datediff in power query

I want to create a conditional or a custom column within Power Query (using M) and NOT DAX, Here is my requirement

 

if [Consent Application Submitted] is not blank and [Consent Application Submitted] > 6 Weeks from today then "White"

 

if [Consent Application Submitted] is not blank and [Consent Application Submitted] < 6 Weeks from today then "Red"

 

I know I can do this using DAX (DATEDIFF). However, I want to create a column using Power Query. 

 

Is there a way to achieve this in Power Query or the M code. 

 

 

 

 

 

 

Show and Tell
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi smjzahid ,

     

    You could refer to the following codes:

    if
Duration.Days(Date.From(DateTime.LocalNow())-[Date])>42
then "White"
else "Red"

     

2 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi smjzahid ,

     

    Try this one:

     

    if [Date] > Date.AddWeeks(Date.From(DateTime.LocalNow()), 6) then
    "White"
    else
    "Red"

     

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi smjzahid ,

     

    You could refer to the following codes:

    if
Duration.Days(Date.From(DateTime.LocalNow())-[Date])>42
then "White"
else "Red"