Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
First Post!
I'm trying to use power query to calculate attach rates between two products Parent and Child in my sales data for an ecommerce business.
My data is organized as traditional transaction data where a single row contains the SKU, Product ID and Line ID, plus additional order detail like qty, sales amount, and customer data.
Demand Order Date | Order ID | Order Line ID | Demand Quantity | Product Name | SKU Code |
1/18/2024 | 4559164994139 | 4576253004139 | 6 | Ethernet Power Adapter - Black | YB5-000010 |
1/18/2024 | 4559164994139 | 4575496254139 | 1 | Blue 3-Pack - Black | HG1-000500 |
1/26/2024 | 4561501054139 | 4577130654139 | 2 | Floor Stand - Black | YB5-000007 |
1/26/2024 | 4561501054139 | 45773211474139 | 1 | Table Stand - Black | YB5-000005 |
1/26/2024 | 4561501054139 | 45773264714139 | 1 | Blue 3-Pack - Black | HG1-000500 |
1/30/2024 | 4562004524139 | 45732373114139 | 1 | Blue 3-Pack - Black | HG1-000500 |
The output I'm hoping for is something like below - producing a list of all SKUs in the datasource, and an attach rate for each other product to that parent product.
Parent | Child | Attach Rate |
Blue 3-Pack - Black | Table Stand - Black | 30% |
Blue 3-Pack - Black | Floor Stand - Black | 15% |
Blue 3-Pack - Black | Ethernet Power Adapter - Black | 20% |
I want the attach rate to be calculated at order level, not by qty of units - i.e. Count of orders containing Blue 3-Pack AND Ethernet Power Adapter, divided by count of orders containing the Blue 3-Pack would give me the attach rate of the Ethernet Adapter to the 3-Pack.
I have not used List functions in Power Query before but I'm thinking maybe this holds the solution?
Thanks!
Solved! Go to Solution.
Hi @ANewman1 ,
I'm not sure I understand you correctly, because I can't calculate your expected result with the data you provided.
I have a few questions. First, how do you determine which is the parent product and which is the child product? I can't tell from the data you provided. Second, how are these percentages calculated? Can you write the calculation formula for the 6 lines of data you provided above? I really can't calculate your results:
I have tried to calculate based on your description and the 6 rows of data you provided. Can you check to see if this is what you want?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFNC8IwDAbgv1J2dpiPprVHB34cB3oR8TC1IDg2GRP/vlEm20FRC6G8UJ6EdLtNcIyTMQHZZJRYkYDOhmCRwzN7R8IAXXZas/YUmyq2Jq9vsTHTY3Fp9U5NVhaHsz7YZJKCHoRkN/rqiw3aosuolZXXaDjNFRugywU+UIEXSq5HHYp2kx71yOBembTmZV03ZtUW1fHNpOB/QpkQrR+Mui72Zfysyo+qsx7/XwDDQCX9IqFeZWLP+J+6uwM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Demand Order Date" = _t, #"Order ID" = _t, #"Order Line ID" = _t, #"Demand Quantity" = _t, #"Product Name" = _t, #"SKU Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", type text}, {"Order Line ID", type text}, {"Demand Order Date", type date}, {"Demand Quantity", Int64.Type}}),
GroupedData = Table.Group(#"Changed Type", {"Order ID"}, {{"Products", each Text.Combine(List.Distinct([Product Name]), ", "), type text}}),
AttachRates =
Table.AddColumn(GroupedData, "Attach Rate", each
let
ParentCount = List.Count(List.Select(GroupedData[Products], (x) => Text.Contains(x, "Blue 3-Pack - Black"))),
ChildCount = List.Count(List.Select(GroupedData[Products], (x) => Text.Contains(x, "Blue 3-Pack - Black") and Text.Contains(x, "Ethernet Power Adapter - Black")))
in
if ParentCount = 0 then 0 else ChildCount / ParentCount
)
in
AttachRates
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ANewman1 ,
I'm not sure I understand you correctly, because I can't calculate your expected result with the data you provided.
I have a few questions. First, how do you determine which is the parent product and which is the child product? I can't tell from the data you provided. Second, how are these percentages calculated? Can you write the calculation formula for the 6 lines of data you provided above? I really can't calculate your results:
I have tried to calculate based on your description and the 6 rows of data you provided. Can you check to see if this is what you want?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFNC8IwDAbgv1J2dpiPprVHB34cB3oR8TC1IDg2GRP/vlEm20FRC6G8UJ6EdLtNcIyTMQHZZJRYkYDOhmCRwzN7R8IAXXZas/YUmyq2Jq9vsTHTY3Fp9U5NVhaHsz7YZJKCHoRkN/rqiw3aosuolZXXaDjNFRugywU+UIEXSq5HHYp2kx71yOBembTmZV03ZtUW1fHNpOB/QpkQrR+Mui72Zfysyo+qsx7/XwDDQCX9IqFeZWLP+J+6uwM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Demand Order Date" = _t, #"Order ID" = _t, #"Order Line ID" = _t, #"Demand Quantity" = _t, #"Product Name" = _t, #"SKU Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", type text}, {"Order Line ID", type text}, {"Demand Order Date", type date}, {"Demand Quantity", Int64.Type}}),
GroupedData = Table.Group(#"Changed Type", {"Order ID"}, {{"Products", each Text.Combine(List.Distinct([Product Name]), ", "), type text}}),
AttachRates =
Table.AddColumn(GroupedData, "Attach Rate", each
let
ParentCount = List.Count(List.Select(GroupedData[Products], (x) => Text.Contains(x, "Blue 3-Pack - Black"))),
ChildCount = List.Count(List.Select(GroupedData[Products], (x) => Text.Contains(x, "Blue 3-Pack - Black") and Text.Contains(x, "Ethernet Power Adapter - Black")))
in
if ParentCount = 0 then 0 else ChildCount / ParentCount
)
in
AttachRates
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
12 | |
8 | |
8 |