How to implement market basket analysis

[Akshay]

We have customer details table with data like

OrderID     ProductName     Sales

1001          a, b                     $20

1002          a,c                      $20

1003          a,b                     $20

1004          a,c                      $20

1005          a,b                     $20

I need a Market Basket as when I select product A, it should display a list as:

Product     Orders

B               3

C               2

 This means, Product B is purchased 3 times with product A and similarly product C is purchased 2 times with product A.

I have a dashborad created in tableau. I need to create a replicate of it in powerbi.

Please follow this link: https://ibb.co/dquarv

[v-huizhn-msft]

Hi @Akshay,

There is no direct market basket analysis visual in Power BI desktop. But you can achieve your requirement by following the steps.

1. Right click the table->Query Editor, split the product name by selecting "split columns"->By Delimiter->comma shown in the screenshot. Then click apply on homw page.



Here is my query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFRQ0lFK1EkCkkYGSrE6YEEjsFgyipgxFnUmWNSZoqiLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrderID = _t, #" ProductName" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {" ProductName", type text}, {"Sales", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", " ProductName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" ProductName.1", " ProductName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{" ProductName.1", type text}, {" ProductName.2", type text}})
in
    #"Changed Type1"

2. Create a slicer including ProductName.1, create a measure using the formula below. Then create a table visual to display the expected result.

Orders = COUNTA(Table1[ ProductName.2])

I upload my .pbix file as attachment, please download and review more details.

Best Regards,
Angelia

[MAAbdullah47]

Thank you for the Post but There is no PBX file attached with this post , can you upload it?