Search code examples
excelpowerbipowerquerypowerbi-desktopm

PowerQuery - All Combinations - Cross Product - Cartesian Product

I have a table in Powerquery (simplified version of my actual table) where I have a product a bomline and a BOMproduct.

Product Bomline Bomproduct
A 1 1.1
A 2 1.2
A 3 1.3

My desired end result of the table would be the following table, do you have any Idea how to accoplish that in PowerQuery? I'm thinking of storing a list of all combinations, but don't know where to start.

Product Bomline Bomproduct All_combinations
A 1 1.1 1.1
A 2 1.2 1.2
A 3 1.3 1.3
A 1 1.1 1.3
A 2 1.2 1.1
A 3 1.3 1.2
A 1 1.1 1.2
A 2 1.2 1.3
A 3 1.3 1.1
Solution

  • This is a simple Cartesian product. If your table looks like this:

    enter image description here

    Add a custom column and write the name of your previous step (mine is called Changed Type).

    enter image description here

    enter image description here

    Then expand the relevant column:

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Bomline", Int64.Type}, {"Bomproduct", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Changed Type"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Bomproduct"}, {"Bomproduct.1"})
in
    #"Expanded Custom"