Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Create a dynamic combo list based on 3 other lists

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Create a dynamic combo list based on 3 other lists

10-13-2015
08:40 AM

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.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-19-2015
02:48 AM

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 |

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-19-2015
02:48 AM

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 |

Announcements

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Featured Topics

Top Kudoed Authors

User | Count |
---|---|

303 | |

99 | |

90 | |

73 | |

67 |