Forum Discussion

andersbq's avatar
andersbq
Frequent Visitor
5 years ago
Solved

Custom column - IF statements within date interval

Hello everyone! I have an ecommerce order table with data that looks like below:

 

Date  EmailHomeloanSales valueNew column (converted)
2020-01-01[email protected]Yes0Yes
2020-01-20[email protected]No150 
2020-01-01[email protected]Yes0No
2020-05-20[email protected]No150 

 

I want to add a custom column (marked in red) in power query that calculates if a "Homeloan" is converted or not.

 

Conversion rule: A homeloan is converted if a purchase (sales value >0) is made within 30 days from homeloan-order (email can be used as common denominator). As per above, first homeloan is converted (purchase made within a month), second one is not.

 

Thanks in advance!!

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Is there a reason this needs to be done in the query editor?  If not, you can do it as a calculated column (or a measure).  Here is a calculated column expression that returns your desired result.

     

     

    Converted =
VAR thisdate = HomeSales[Date  ]
VAR saledate =
    CALCULATE (
        MIN ( HomeSales[Date  ] ),
        ALLEXCEPT (
            HomeSales,
            HomeSales[Email]
        ),
        HomeSales[Date  ] > thisdate,
        HomeSales[Sales value] > 0
    )
VAR daysbetween =
    DATEDIFF (
        thisdate,
        saledate,
        DAY
    )
RETURN
    IF (
        HomeSales[Homeloan] = "Yes",
        IF (
            daysbetween <= 30,
            "Yes",
            "No"
        )
    )

     

    Regards,

    Pat

     

     

8 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Is there a reason this needs to be done in the query editor?  If not, you can do it as a calculated column (or a measure).  Here is a calculated column expression that returns your desired result.

     

     

    Converted =
VAR thisdate = HomeSales[Date  ]
VAR saledate =
    CALCULATE (
        MIN ( HomeSales[Date  ] ),
        ALLEXCEPT (
            HomeSales,
            HomeSales[Email]
        ),
        HomeSales[Date  ] > thisdate,
        HomeSales[Sales value] > 0
    )
VAR daysbetween =
    DATEDIFF (
        thisdate,
        saledate,
        DAY
    )
RETURN
    IF (
        HomeSales[Homeloan] = "Yes",
        IF (
            daysbetween <= 30,
            "Yes",
            "No"
        )
    )

     

    Regards,

    Pat

     

     

    • andersbq's avatar
      andersbq
      Frequent Visitor
      5 years ago

      Thanks mahoneypat. Reason why I wanted it done in query editor is because I put together the data table there through merging and grouping data. However maybe it's easier to create this column as a DAX formula. Your formula works really well! Although for all the rows where homeloan=Yes and no actual purchase was made in the future (i.e. no record of that email adress in the future), the formula also counts it as a conversion. I added this which seems to work:

       

      IF (
      AND(
      daysbetween <= 30, daysbetween > 0),
       
      Thanks again!
  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi andersbq 

    Paste this M code in a blank query to see the steps. It could be just done in one step but I've split it in more so that it is easier to follow. You could merge all the steps in one if needed.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MAQiJR2lrPyMPIeU/FS95PxcIDcytRhIGijF6iDUGRlgqvPLBxKGpqgKIQYm5qXiNdAUaiCqOoSBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Email = _t, Homeloan = _t, #"Sales value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type",(inner)=> inner[Email] = [Email])[Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Duration.Days(List.Max([Custom])-List.Min([Custom]))>30 then "Yes" else "No"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Converted", each if List.Min([Custom]) = [Date] then [Custom.1] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"})
in
    #"Removed Columns"

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • andersbq's avatar
      andersbq
      Frequent Visitor
      5 years ago

      Thank you AlB - I tried to apply this to my data set but gave up since it was loading/processing for an hour. 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, andersbq 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column with the following m codes.

    let 
email=[Email],
date=Table.Max(
    Table.SelectRows(#"Changed Type",each [Email]=email and [Sales value]>0),
    "Date"
)[Date],
diff=Duration.Days(date-[Date])
in 
if [Homeloan]="Yes"
then if diff<=30 
     then "Yes"
     else "No"
else null

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • andersbq's avatar
      andersbq
      Frequent Visitor
      5 years ago

      hey v-alq-msft! thank you so much for this. It works pretty good, but let's assume there is an email with only a homeloan-order (see printscreen below). The formula then gives me an error. In this scenario it should count as non converted, i.e. "No".

       

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        5 years ago

        Hi, andersbq 

         

        You may try creating a custom column with the following codes. The pbix file is attached in the end.

        let 
email=[Email],
tab=Table.Max(
    Table.SelectRows(#"Changed Type",each [Email]=email and [Sales value]>0),
    "Date"
)
in 
if tab is null
then "No"
else
if [Homeloan]="Yes"
then if Duration.Days(tab[Date]-[Date])<=30 
     then "Yes"
     else "No"
else null

         

        Result:

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.