Create a custom table from 3 dimensions

[SMaz]

Hi all,

I would like to create a 3 columns table in Power Query giving the complete combinations from 3 dimensions. E.g.:

- Agent : Bob, Cathy, Bernie (actually this list could contain 100 names)

- Year : 2021, 2022, 2023

- Week : 1 to 52

The result should give something like :

Year	Week	Agent
2021	1	Bob
2021	1	Cathy
2021	1	Bernie
2021	2	Bob
2021	2	Cathy
2021	2	Bernie
2021	3	Bob
2021	3	Cathy
2021	3	Bernie

and so on, until 2023; 52; Bernie.

Any tips?

Thanks a lot !

[ThxAlot]

Enjoy elegance of Power Query!

let
    Agents = {"Bob","Cathy","Bernie"},
    Years = {2021 .. 2023},
    Weeks = {1 .. 52},

    #"Cartesian Product" =
        List.Accumulate(
            {Agents,Weeks,Years},
            {{}},
            (s,c) => List.TransformMany(c, each s, (x,y) => {x} & (if Value.Is(y, type list) then y else {y}))
        ),

    #"To Table" = Table.FromRows(#"Cartesian Product",{"Yr","Wk","Agt"})
in
    #"To Table"

[AlexisOlson · Accepted solution]

This is called a Cartesian product or a cross-join.

You can use this method twice for 3 dimensions:
Cross join - Power Query | Microsoft Learn

[SMaz]

So easy 🙂

Thanks a lot !