- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

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
- Re: Create a dynamic combo list based on 3 other l...

Topic Options

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

XavJSY

New Member

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

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

Accepted Solutions

Highlighted

jclark88

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- 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

jclark88

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- 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 |