Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I need to create a dynamic list feeding from 3 different lists:
Say I have the 3 following lists X,Y and Z currently containing the following values:
X: a b c
Y: A B C
Z: 1 2 3
and I need to create the dynamic list "Combo X,Y,Z" showing all possible combination of values in X,Y and Z:
a A 1
a A 2
...
c C 3
and I need "Combo X,Y,Z" to update automatically whenever I modify X, Y or Z.
The list I have are all Excel tables, would anyone know if that's possible to do in Excel and if not in PBI and how I could train myself to code that?
Thanks in advance
Solved! Go to Solution.
In SQL i'd normally use a cross join to create the cartesian product of all values - basically, joining every row with every other row. I'm not sure this is possible in power query, but you can trick it by creating a custom column for all input tables that has the same value, then doing some joins on these columns (all the values are the same so every row will join with every other row)..
I've knocked together the power query here (might be a more effecient way but this seems to work):
let //Our source tables, replace with whatever, just make sure the Colum header is "Val" X = Table.FromList({"a","b","c"}, null, {"Val"}), Y = Table.FromList({"A","B","C"}, null, {"Val"}), Z = Table.FromList({"1","2","3"}, null, {"Val"}), //Adding custom column for join X_2 = Table.AddColumn(X, "JoinCol", each 1), Y_2 = Table.AddColumn(Y, "JoinCol", each 1), Z_2 = Table.AddColumn(Z, "JoinCol", each 1), //Join all the tables together Join_1 = Table.NestedJoin(X_2,"JoinCol",Y_2,"JoinCol","Y"), Join_2 = Table.NestedJoin(Join_1,"JoinCol",Z_2,"JoinCol","Z"), //Remove the custom column RemoveCol = Table.RemoveColumns(Join_2,{"JoinCol"}), //Expand out our Y and Z columns ExpandY = Table.ExpandTableColumn(RemoveCol, "Y", {"Val"}, {"Y.Val"}), ExpandZ = Table.ExpandTableColumn(ExpandY, "Z", {"Val"}, {"Z.Val"}), //Rename the columns to something we expect RenameColumns = Table.RenameColumns(ExpandZ,{{"Val", "X"}, {"Y.Val", "Y"}, {"Z.Val", "Z"}}) in RenameColumns
This outputs a table like this:
X,Y,Z
a | A | 1 |
a | A | 2 |
a | A | 3 |
a | B | 1 |
a | B | 2 |
a | B | 3 |
a | C | 1 |
a | C | 2 |
a | C | 3 |
b | A | 1 |
b | A | 2 |
b | A | 3 |
b | B | 1 |
b | B | 2 |
b | B | 3 |
b | C | 1 |
b | C | 2 |
b | C | 3 |
c | A | 1 |
c | A | 2 |
c | A | 3 |
c | B | 1 |
c | B | 2 |
c | B | 3 |
c | C | 1 |
c | C | 2 |
c | C | 3 |
In SQL i'd normally use a cross join to create the cartesian product of all values - basically, joining every row with every other row. I'm not sure this is possible in power query, but you can trick it by creating a custom column for all input tables that has the same value, then doing some joins on these columns (all the values are the same so every row will join with every other row)..
I've knocked together the power query here (might be a more effecient way but this seems to work):
let //Our source tables, replace with whatever, just make sure the Colum header is "Val" X = Table.FromList({"a","b","c"}, null, {"Val"}), Y = Table.FromList({"A","B","C"}, null, {"Val"}), Z = Table.FromList({"1","2","3"}, null, {"Val"}), //Adding custom column for join X_2 = Table.AddColumn(X, "JoinCol", each 1), Y_2 = Table.AddColumn(Y, "JoinCol", each 1), Z_2 = Table.AddColumn(Z, "JoinCol", each 1), //Join all the tables together Join_1 = Table.NestedJoin(X_2,"JoinCol",Y_2,"JoinCol","Y"), Join_2 = Table.NestedJoin(Join_1,"JoinCol",Z_2,"JoinCol","Z"), //Remove the custom column RemoveCol = Table.RemoveColumns(Join_2,{"JoinCol"}), //Expand out our Y and Z columns ExpandY = Table.ExpandTableColumn(RemoveCol, "Y", {"Val"}, {"Y.Val"}), ExpandZ = Table.ExpandTableColumn(ExpandY, "Z", {"Val"}, {"Z.Val"}), //Rename the columns to something we expect RenameColumns = Table.RenameColumns(ExpandZ,{{"Val", "X"}, {"Y.Val", "Y"}, {"Z.Val", "Z"}}) in RenameColumns
This outputs a table like this:
X,Y,Z
a | A | 1 |
a | A | 2 |
a | A | 3 |
a | B | 1 |
a | B | 2 |
a | B | 3 |
a | C | 1 |
a | C | 2 |
a | C | 3 |
b | A | 1 |
b | A | 2 |
b | A | 3 |
b | B | 1 |
b | B | 2 |
b | B | 3 |
b | C | 1 |
b | C | 2 |
b | C | 3 |
c | A | 1 |
c | A | 2 |
c | A | 3 |
c | B | 1 |
c | B | 2 |
c | B | 3 |
c | C | 1 |
c | C | 2 |
c | C | 3 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |