Add a Custom Column to and name it List1. Now, create a query that imports the List2 table and Edit the query. In this method, we still need to load both tables into power query, but we will only need to create one custom column and we don’t need to create a merge query.Ĭreate a connection only query that imports the List1 table. Since writing this post, I’ve found a slightly quicker method to do this. We can also iterate this process if we have more than two tables to find all possible combinations items from 3 or more lists. For Large lists this query becomes a massive time saver though! With small tables like this example, we could have easily created a table manually. Now we have a table which contains every possible combination of items from List1 and List2. Now Close & Load the query from the Home tab. Now we can expand the List2 table in our query. Right click on the column heading and select Remove. We can delete the Join Column now as we don’t really need it anymore and the output will be less cluttered if we remove it. Now we can set up our merge query to join the two lists. Select Combine Queries from the menu and then select Merge from the submenu. Go to the Data tab and select Get Data from the Get & Transform Data section. Now we are ready to join these two lists together using a Merge query. Join the Two Connection Only Queries with a Merge Query # You should have two connection only queries called List1 and List2. In the Import Data dialog box, select Only Create Connection and then press the OK button. Go to the Home tab in the query editor and press the lower part of the Close & Load button then select Close & Load To from the menu. This can be any text or number, just make sure it’s the same in both list queries. In the Custom Column dialog box add a constant formula. Select a cell in your list and then go to the Data tab and select the From Table/Range command in the Get & Transform Data section.įrom the query editor go to the Add Column tab and select Custom Column from the menu. We will then use this column to join our two tables together using a merge query. Create a Connection Only Query for the Two Lists #įor each table, we’re going to create a connection only query that adds a column with the same constant value in each row. In this example I’ve set up two lists and created tables from them called List1 and List2.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |