Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
ANewman1
New Member

Calculate Product Attach Rates via Power Query

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 DateOrder IDOrder Line IDDemand QuantityProduct NameSKU Code
1/18/2024455916499413945762530041396 Ethernet Power Adapter - BlackYB5-000010
1/18/2024455916499413945754962541391Blue 3-Pack - BlackHG1-000500
1/26/2024456150105413945771306541392 Floor Stand - BlackYB5-000007
1/26/20244561501054139457732114741391 Table Stand - BlackYB5-000005
1/26/20244561501054139457732647141391Blue 3-Pack - BlackHG1-000500
1/30/20244562004524139457323731141391Blue 3-Pack - BlackHG1-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.

ParentChildAttach Rate
Blue 3-Pack - Black Table Stand - Black30%
Blue 3-Pack - Black Floor Stand - Black15%
Blue 3-Pack - Black Ethernet Power Adapter - Black20%

 

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

vjunyantmsft_0-1728526695407.png


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

vjunyantmsft_1-1728526936287.png


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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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:

vjunyantmsft_0-1728526695407.png


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

vjunyantmsft_1-1728526936287.png


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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.