Forum Discussion

SMaz's avatar
SMaz
Frequent Visitor
2 years ago
Solved

Create a custom table from 3 dimensions

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 :

YearWeekAgent
20211Bob
20211Cathy
20211Bernie
20212Bob
20212Cathy
20212Bernie
20213Bob
20213Cathy
20213Bernie

 

and so on, until 2023; 52; Bernie.

 

Any tips?

Thanks a lot !

Need Help

3 Replies

    • SMaz's avatar
      SMaz
      Frequent Visitor
      2 years ago

      So easy 🙂

      Thanks a lot !

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago

    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"