Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone! I have an ecommerce order table with data that looks like below:
Date | Homeloan | Sales value | New column (converted) | |
2020-01-01 | john@doe.com | Yes | 0 | Yes |
2020-01-20 | john@doe.com | No | 150 | |
2020-01-01 | jane@doe.com | Yes | 0 | No |
2020-05-20 | jane@doe.com | No | 150 |
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!!
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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:
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.
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".
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.
@v-alq-msft works well! Many thanks! However when trying to close&apply, it loads for hours without finishing..Maybe too much data for this type of calculation. The dax formula @mahoneypat suggested is possibly the way to go.
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
Thank you @AlB - I tried to apply this to my data set but gave up since it was loading/processing for an hour.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.