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) ...
Need Help
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"