CODEWITHSUNDEEP
excelpowerbipowerquery
Tom
Tom

Reputation: 1

Power Query/PowerBi:Table of 2 or more Items appearing together in a transaction

I'm trying to get a table of Rates of Item Ids occuring together. I have a table in Excel that is a unique list of IDs in rows and across the columns. I then count and divide the Sales Order numbers in which they match and don't match. I'm trying to replicate this in power bi, but I'm still a little to new to pulling it off. I've tried pivoting in a few ways, but I'm not sure i'm on the right path.

Basically going from

Products|Sales Orders

A | 1

B | 1

A | 2

B | 3

C | 4

into something like

| A |   B |    C |

A| 1.0| 0.33 | 0.0

B| 0.33| 1.0 |0.0

C| 0.0 | 0.0 |1.0|

any help is really appreciated

This is an example of the table I currently use to do this. I'm just counting sales orders that match up with the products

Upvotes: 0

Views: 100

Answers (1)

MarcelBeug
MarcelBeug

Reputation: 2997

My suggestion with 3 queries:

  1. Table1 (input).

  2. OrdersPerProduct: table with unique products and nested lists of their unique orders.

  3. Results: resulting in the required output.

Query OrdersPerProduct:

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"Orders", each List.Distinct(_[Order]), type list}})
in
    #"Grouped Rows"

Query Results:

Note the last step with a dynamic "Change Type" of all Product columns

let
    Source = OrdersPerProduct,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Orders"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "OtherProduct", each #"Removed Columns"[Product], type {text}),
    #"Expanded OtherProduct" = Table.ExpandListColumn(#"Added Custom", "OtherProduct"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded OtherProduct",{"Product"},OrdersPerProduct,{"Product"},"ProductOrders",JoinKind.LeftOuter),
    #"Expanded ProductOrders" = Table.ExpandTableColumn(#"Merged Queries", "ProductOrders", {"Orders"}, {"ProductOrders"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded ProductOrders",{"OtherProduct"},OrdersPerProduct,{"Product"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Orders"}, {"OtherProductOrders"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Rate", each List.Count(List.Intersect({[ProductOrders],[OtherProductOrders]})) / List.Count(List.Distinct(List.Combine({[ProductOrders],[OtherProductOrders]})))),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ProductOrders", "OtherProductOrders"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[OtherProduct]), "OtherProduct", "Rate"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",List.Transform(#"Pivoted Column"[Product], each {_, type number}))
in
    #"Changed Type"

Upvotes: 0

Related Questions