Power Query is a tool included natively in Excel 2016 because it expands the possibilities of work, especially when it comes to managing large databases. This solution has been well received by a large number of users.
However, if you haven’t had a chance to test it, today we offer a possibility to use it for table combinations, allowing you to find data matches between them in simple steps.
How to combine tables in Excel with Power Query?
For example, there are formulas that let you convert numbers to letters in Excel, which works well in a small or medium-sized database, but when it’s a large database, there are situations where formulas can cause inefficiencies.
For this, Microsoft Excel includes a very powerful tool that can process high-level information quickly and accurately.We are talking about Power Query, its main function is Connect different data, perform actions Convert, merge or export joints.
It is a tool in itself that deserves a detailed study of its understanding and use. Today, we’ll focus on its possibility to provide a type of data composition, by which we mean inner composition, where it evaluates matching rows to return them to a new table.
unconditional merger
At this level, you most likely already know how to insert elements from Excel to PowerPoint, but probably don’t know how to manipulate Power Query because it’s designed for slightly more complex tasks, but that doesn’t mean it’s used the same way of.In fact, just a few simple steps Allows you to combine datadiscarding the more popular options as formula conditions.
In this type of formula, the process can get a little tangled and difficult when multiple variables need to be considered. If you find yourself needing to find results by evaluating different matches in a table, consider applying some combination of data through Power Query before considering detailed and extensive formulas.
Combination by selection
Combinations of data in tables with up to thousands of rows are available in just a few clicks, which is useful if you want to get results quickly, which also avoid inconvenience, Especially if your computer is low on memory. It’s no secret that Excel can have major flaws when it comes to calculating books with too much information.
Before starting Inner Join, some basic things you have to make sure are correcting the spelling in the spreadsheet so that the resulting table will be ready to be exposed to the end consumer without errors of any kind.
What is the procedure for combining Inner Join?
So let’s clarify, the Power Query composition process provides different outputs, but if you need it, Evaluate one table against another to find a match In one of the columns, the Inner Join method is the most feasible. Now focusing on the steps you have to perform, we will summarize:
- Be careful to download Excel from its official page, then run it with the book that contains the table you want to evaluate.
- If it doesn’t matter that they are in different sheets, make sure they are created as «datasheets» within the platform. If not, position yourself in one of the cells it contains (do it alone), press «CTRL + T» and the dialog automatically preselects the range, marking OK if it is correct. Under Design > Table Name, give each table a name.
- Now position yourself in the first data cell of the table, which you want to compare with the second data cell.
- Next is you go to the «Data» menu and click «From Table or Range». In the Query Editor, select Close & Load > Close & Load in the upper left corner.
- In the dialog box make sure «Create connection only» is checked and OK. Repeat the process using the comparison table.
- Now go back to the Data tab and select Get Data > Combine Queries > Combine.
- In the first list, you must select the name of the table to compare and click on the column to evaluate.
- In the second list, select Compare Table, and select the columns to match. As «Combination Type», select Internal and accept.
How do I extend the newly created column for my OnlineSales?
You’ll notice that when you click OK in the Merge window, you manage to open the Query Editor window again, showing Relationship between the first table and the second table. It can be confusing at first because the comparison table, the second one, is condensed.
Since we’re interested in evaluating all the data directly, we’ll go ahead and expand, we just click the icon to the right of the same header cell. In the options shown, uncheck Evaluate matching columns, prefix with name and accept. You will see the full results of the query, then click Close and Load.
You will get a new sheet in the workbook with the result of the requested data merge, On it, you can apply different formatting as needed, such as sorting by the evaluation column, to verify matches more easily. It’s important to practice and master this tool, especially if you’re dealing with OnlineSales, because its functionality is so important.