Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !
Solved! Go to 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
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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
So easy 🙂
Thanks a lot !