Bạn đang xem bài viết Join Two Or More Tables In Excel With Power Query được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
In this tutorial, we will look at how you can join tables in Excel based on one or more common columns by using Power Query and Merge Tables Wizard.
Combining data from multiple tables is one of the most daunting tasks in Excel. If you decide to do it manually, you may spend hours only to find out that you’ve messed up important information. If you are an experienced Excel pro, then you can possibly rely on VLOOKUP and INDEX MATCH formulas. A macro, you believe, could do the job in no time, if only you knew how. The good news for all Excel users – Power Query or Merge Tables Wizard can be your time-saver. The choice is yours.
How to join tables with Excel Power Query
In simple terms, Power Query (also known as Get & Transform in Excel 2016 and Excel 2019) is a tool to combine, clean and transform data from multiple sources into the format you need such as a table, pivot table or pivot chart.
Among other things, Power Query can join 2 tables into 1 or combine data from multiple tables by matching data in columns, which is the focus of this tutorial.
For the results to meet your expectations, please keep in mind the following things:
Power Query is a built-in feature in Excel 2016 and Excel 2019, but it can also be downloaded in Excel 2010 and Excel 2013 and used as an add-in. In earlier versions, some windows may look different from the images in this tutorial that were captured in Excel 2016.
For the tables to be combined correctly, they should have at least one common column (also referred to as a common id or key column or unique identifier). Also, the common columns should contain only unique values, with no repeats.
The source tables can be located on the same sheet or in different worksheets.
Unlike formulas, Power Query does not pull data from one table to another. It creates a new table that combines data from the original tables.
The resulting table does not update automatically. You should explicitly tell Excel to do this. Please see how to refresh a merged table.
As an example, let’s join 3 tables based on the common columns Order ID and Seller. Please note that our tables have different numbers of rows, and although table 1 has duplicates in the Seller column, table 3 contains only unique entries.
Our task is to map the data in table 1 with the relevant records from the other two tables, and combine all the data into a new table like this:
Table 1 is named Orders
Table 2 is named Products
Table 3 is named Commissions
Create Power Query connections
Not to clutter your workbook with copies of your original tables, we are going to convert them into connections, do the merge within the Power Query Editor, and then load only the resulting table.
To save a table as a connection in Power Query, here’s what you do:
Select your first table (Orders) or any cell in that table.
This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.
Repeat the above steps for all other tables you want to merge (two more tables, Products and Commissions, in our case).
When finished, you will see all the connections on the pane:
Merge two connections into one table
With the connections in place, let’s see how you can join two tables into one:
In the Merge dialog box, do the following:
Select your 1st table (Orders) from the first drop-down.
Select your 2nd table (Products) from the second drop-down.
In the Join Kind drop-down list, leave the default option: Left Outer (all from first, matching from second).
Upon completion of the above steps, the Power Query Editor will show your first table (Orders) with one additional column named like your second table (Products) added to the end. This additional column does not have any values yet, just the word “Table” in all the cells. But don’t feel discouraged, you did everything right, and we are going to fix that in a moment!
Select the columns to add from the second table
At this point, you have a table resembling the one in the screenshot below. To complete the merging process, perform the following steps within the Power Query Editor:
In the box that opens, do this:
Keep the Expand radio button selected.
Unselect all columns, and then select only the column(s) you want to copy from the second table. In this example, we select only the Product column because our first table already has Seller and Order ID.
Uncheck the Use original column name as prefix box (unless you want the column name to be prefixed with the table name from which this column is taken).
As the result, you will get a new table that contains every record from your first table and the additional column(s) from the second table:
If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.
Merge more tables (optional)
In case you have three or more tables to join, there is some more work for you to do. I will outline the steps briefly here, because you have already done all this when joining the first two tables:
Save the table you’ve got in the previous step (shown in the screenshot above) as a connection:
The screenshot below shows my settings:
In this example, we add only the Commission column:
As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.
Import the merged table to Excel
With the resulting table in the Power Query Editor, there is just one thing left for you to do – load it in your Excel workbook. And it is the easiest part!
In the Import Data dialog box, select Table and New Worksheet options.
A new table combining the data from two or more sources appears in a new worksheet. Congratulations, you did it!
As a finishing touch, you may want to apply the right number format to some columns and maybe change the default table style to your favorite one. After these improvements, my combined table looks very nice:
In the previous example, we were combining tables by matching data in one key column. But there is nothing that would prevent you from selecting two or more column pairs. Here’s how:
After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.
Merge Tables Wizard – quick way to join 2 tables in Excel
Now that you are familiar with the inbuilt tool, let me show you our approach to merging tables in Excel.
In this example, we will be combining the same tables that we joined with Power Query a moment ago. I have just added a few more rows to the second table to show you more capabilities of our add-in:
With the Merge Tables Wizard installed in your Excel, here’s what you need to do:
Please notice the Case-sensitive matching box at the top. Select it if you want to treat uppercase and lowercase text in the key columns as different characters. For this example, we don’t need that, so we leave the box unselected.
We select the Seller column because we have more rows in the second table and we want the new seller names to appear in the existing Seller column:
This step is very important because it determines how your tables will be merged. In this example, we go with the default options shown in the screenshot below. But I’d like to draw your attention to the following 2 boxes that can prevent overwriting your existing data in case you’ve chosen to update some columns:
Empty cells only
Only if cells in the lookup table contain data
With the default options, the wizard highlights the newly added rows and adds the Status column. If you don’t want any of that, clear the corresponding boxes in the last step.
To join three and more tables, simply repeat the above steps. Just remember to select the result of a previous merge as your main table.
This example has shown just one scenario that our wizard can handle, but there is much more to it! If you are curious to know other use cases, please check out these examples.
Also, you can download a a trial version of Ultimate Suite for Excel that includes Merge Tables Wizard as well as 60+ other useful tools.
In case you are looking to join tables in some other way, you may find the following resources useful.
Other ways to combine data in Excel:
Merge tables by column headers – join two or more tables based on column names. You can choose to combine all the columns or only the ones you select.
Combine multiple worksheets into one – copy multiple sheets into one summary worksheet. Of course, it’s not manual copy/pasting! You only indicate which worksheets to merge, and our Copy Sheets tool does the rest.
Compare two Excel files – how to compare two tables (worksheets) for differences and merge them into a single sheet.
Fun Word Games For Two Or More Players, Including Groups
Words, wordplay, reading, and writing have been favorites of Liz’s since early childhood. She enjoys exploring science and science fiction.
Word Games Are Fun Anywhere
This group of games is very adaptable. There can be as few as two players or as many as 5 or more. My mother and I used to play them all the time, usually while passing time waiting for the start of a play or to be called in for an appointment.
These are all on the order of more “old-fashioned” games, requiring only the use of the brain and, in some cases, pencil and paper. No batteries required or included.
Game One: “Think Fast!”
This game can be played repeatedly, but it’s the most fun when pulled as a surprise, with the other party not expecting it.
Basic Game Play:
Simply say aloud a category of some physical thing, be it flowers, furniture, food, etc. (Gee, I got on a roll with the “F” words, there, didn’t I?-heh, heh.)
Once you have stated the category, immediately begin counting quite rapidly from one to ten; it should take you 5 seconds or less. The other person(s) have only until then to name such an item.
For example, I call out, “Flowers! 1-2-3-4-5-6-7-8-9-10!” If the other player is on their toes, they may make it by the time “5” is called to shout out the name of a flower-“zinnia,” for instance. I say “shout out,” as, in the urgency presented, shouting is usually what happens. If they cannot think of something before the time runs out, they lose, and it is again the first player’s turn to call out a category. However, if they beat the clock, it is their turn to call a category.
This is one of those games to play at home or in your car. You can play back and forth, of course, with the element of surprise missing, and eventually, someone cannot think of anything in time, or experiences a “fail,” by calling out an item in the wrong category, such as “butter,” when the category was flowers. Bzzzzzzttt!! Wrong! Thank you for playing!
This is a good ‘thinking cap’ game for kids, keeping it simple and to things they’d know, of course.
Game Two: “Same Letter”
My mother and I played this one a lot! It was probably our favorite ‘while waiting’ game. Each answer must begin with the final letter of the previous answer.
Basic Game Play:
You begin with a category, as with the first game, but there are no time limits, unless you choose to impose one. With only two players, it’s not really needed, but if playing with a group and taking turns around a circle of players, it may be best to impose a time limit of, say, 20 or 30 seconds in which to answer.
Categories can be anything you choose, with geography probably offering the widest possible choice of answers. You can pre-define the geographic area to local (your own state should be the smallest area used), national, or worldwide. You can further define whether you want to limit answers to just city names, or both cities and countries, and you may include features such as mountain ranges or anything else that has been given a name.
You’d have access, in the latter case, to giving answers such as “Mount Rushmore,” or “India,” or “Lake Huron.” Mom and I usually played a worldwide version, limited to cities and countries.
Specifics of Play:
Once the category has been established and defined to everyone’s satisfaction, it’s time to play. No repetition of prior answers is allowed.
Player one starts by naming something in the given category; I’ll use geography for my examples (mom and I also allowed old/archaic place names, but you don’t have to):
Player two responds with a different place that must begin with the final letter of the previous answer:
Play returns to player one in a two-person game, or continues to the next player if multiple people are playing:
And so forth . . .
The game ends when either player in a two-player game is stumped and cannot think of a fitting answer, gives a wrong-category answer, or one that is outside the predetermined rules, or repeats an answer that has already been stated.
In a multiple player game, the above mistakes result in that player being out of the game. The play continues until there is only a single player left, or until the final two or three mutually decide to call it quits.
This game can get quite funny, especially if you get stuck in a loop of words/places all beginning and ending with the same letter. You would not believe how many cities and countries fit into this pattern:
Algeria, Altoona, Atlanta, Alaska, Alabama, etc. There are many more, trust me. Usually, you don’t even realize it until you’ve spoken your answer, thinking you were so smart to come up with something . . . and thenafter 2 or 3 such cases, the laughter begins.
The game can be tailored for school-age children, and it’s a great way to subtly help them remember how to spell things. Learning should be fun!
Game Three: “Remember What to Pack!”
This is an alphabet-sequence-driven game of memory, and it’s suitable for just two players, but it’s more fun if several are involved. It is also known by the name, “Going on a Trip.”
Basic Game Play:
Begin by stating a travel destination, “France,” for instance. Play begins with the first person stating (for example), “I’m going on a trip to France, and I’m going to take an apple.”
The next player must repeat the sequence, and add an item from the next letter in the alphabet: “I’m going on a trip to France, and I’m going to take an apple and a briefcase.”
The third player (or first again, if only two people) proceeds in turn: “I’m going on a trip to France, and I’m going to take an apple, a briefcase and a camera.”
In this game, the player gets “buzzed” and is out of the game if they forget to repeat the “I’m going on a trip to . . . ” sequence.
Play follows this pattern until the end of the alphabet is reached. The game ends for two people when someone goofs or gets stumped. For multiple players, a mistake removes that person from the game; the “winner” is the last player to remain when/if “Z” is reached.
This game helps memory skills, and also reinforces the importance of focusing and paying attention.
It can send people to the dictionary (not during the game-that’s cheating!) to discover things to “pack” besides Zebras, Yaks and Xylophones when that end of the alphabet is reached.
Game Four: “How Many Words?”
This is a pencil and paper game, and one I use to this day while waiting on medical appointments. Anyone who has to spend much time in waiting rooms knows full well the futility of finding anything of interest to read in the outdated magazines sitting there. And, sadly, as we age, we seem to spend more time sitting in waiting rooms.
Basic Game Play:
All you need is a word. Any word, but the bigger the word, the better. The object is to use the letters within that word, (I call it a “seed word”), and re-arrange them to form as many other words as you can. There is no “end” or time limit. Your time ends when you are called for your appointment, or when you are stumped; that’s all.
You may use only letters that appear in the original word
You may use the letters to make other words only as often as they appear in your source word.
You may choose your own rules as to whether one and two letter words are allowed, or whether you must make only words with three or more letters
Archaic and obscure words are allowed (such as those you might encounter in crossword puzzles)
You may choose, if the original letters permit, to include both singular and plural forms for the words you make, that’s up to you
You may set a time limit if you wish, but I usually do not
For example: if I choose the short word, “carpet,” I can make the following list:
And so on; you get the idea. It is up to you to decide the rules as far as limiting or not limiting number of letters that must be in the discovered words. I place no limits, although most traditional word games to require a minimum word length of three letters. This eliminates single-letter words such as “A” a or “I” as well as two-letter words such as “it” and “if.”
If playing with a group, the “winner” is the person who found the most legitimate words.
Alternately, players can read off their lists in turn, and, as with the game “Scattergories,” all must cross out any word ont their list that matches any other person’s word, so the focus is on originality. With more than a few players, however, this version becomes rather time-consuming.
Sometimes, I even play this in my head, if I’m having trouble falling asleep, instead of counting those boring old sheep! Here’s one I used in that situation recently:
No “a’s” and no “i’s” in that word, and only singletons of the other vowels; in fact, no repeating letters at all. I did get a pretty good list out of it despite that. Let me know how you do with it. (My partial list follows at the end.)
“How Many Words?” K.I.S.S. Principle When Kids Are Playing
I disagree with rules limiting word length, especially if kids are playing. You don’t want them frustrated when the challenge is “find as many words as you can.” Besides, by finding those easiest of words first, it gets the juices flowing, and instills an early feeling of success. So, Keep it simple!
For school settings, I would place a time limit, say ten minutes–adjusting that up or down as appropriate for grade level. This is a good game for spelling, memory, vocabulary, and rapid spotting with the eyes, as you “scroll” back and forth looking for likely words.
For school settings, you can have the “winner(s)” be those who came up with the most legitimate words.
This doesn’t quite qualify as a word game, and it’s better for adults, who probably have many more years of movie-watching under their belts. Anyone can play, but it’s best played by movie geeks!
I used to belong to a theater group, and several of them would play this all the time after rehearsals, and I could not keep up!
Player 1 names a movie-any movie at all-for example, Hunt for Red October.
Player 2 must come up with the name of an actor or actress who was in that movie. It need not be one of the main stars, but can be-for example, “Tim Curry.”
Player 3 (or back to player 1, if only two are playing) must then name another movie in which that actor played-for example, Rocky Horror Picture Show.
Player 4 (or back to player 2, if only two are playing) must name another actor or actress in that movie, etc.-for example, “Susan Sarandon” and so forth.
Play continues until someone is stumped or gives a wrong answer. In the case of multiple players, the one who goofed is out; and play continues until there are only 2 left.
Game ends either when one of the last two makes a mistake or gets stuck, or by mutual agreement.
(You can also begin the game the other way around, as well, by naming an actor first instead of a movie title, but the rest of the play follows the same pattern.)
The two best players in the group I was with could come up with the most obscure actors that most of us have never even heard of. I swear, they must have sat there and memorized all the credit rolls! There were also a lot of obscure “B” movie titles tossed about. Those two could play until all night if the mood struck them!
And Now, That List . . .
Here are the words I got out of “desultory:”
dolt, sultry, sold, yet, toy, sod, us, use, used, yes, lot, let, lose, lost, toe, toed, tole, tor, tore, led, red, redo, rode, rot, rote, rust, rusty, rod, rut, rout, route, rose, ruse, rest, resort, sue, suet, soy, ley, lute, lye, try, tory, slot, sued, sled, sly, douse, dust, dusty, lust, lusty, old, ole, os, do, doe, dot, dye, yet, yule, ted, slut, slue, sly….
That’s a starter: there are quite a few others!
When I’m doing the game in my head, it comes out a lot as you see it above. When I use pen and paper, I try to keep the words by alphabetical order–though not strictly–just by first letter, starting with “how many words starting with ‘a’ can I find?” Then I move on to the next letter that follows in alphabetical sequence, and so on. Since there is no a, b, or c in the word I chose above, my starting letter was ‘d,’ and on from there.
© 2012 Liz Elias
Liz Elias (author) from Oakley, CA on December 15, 2012:
Yes, we often played such games on car rides, including “not quite word games” such as 20 questions.
I don’t think I’m familiar with “How Many Words?” I’d be interested to hear about that chúng tôi perhaps I know it by another name.
Thanks for stopping by and adding a point of interest. Cheers!
Susan Zutautas from Ontario, Canada on December 15, 2012:
I too like word games and remember playing How many Words. They are sure great to pass the time especially on long car rides.
Liz Elias (author) from Oakley, CA on December 13, 2012:
Thank you so much–I’m most pleased that you find these worthwhile games for a youth group. I do think they need a “disconnect” from electronic devices every now and then!
Sarah Carlsley from Minnesota on December 13, 2012:
These are great! Would be wonderful as easy youth group games while we’re waiting!
Liz Elias (author) from Oakley, CA on December 11, 2012:
Dora Weithers from The Caribbean on December 11, 2012:
Great ideas especially the games you can play alone. These ideas will come in handy. Thanks for sharing.
Liz Elias (author) from Oakley, CA on December 10, 2012:
@ GiblinGirl, Thanks for stopping by–I’m delighted that you enjoyed these suggestions of time-killing yet mentally stimulating games. Have fun with them!
drbj and sherry from south Florida on December 10, 2012:
What great ideas for word games, Lizzy, thanks for reminding me what fun they can be. You are anything but Dzy, m’dear.
GiblinGirl from New Jersey on December 10, 2012:
I LOVE word games – I play Scrabble all the time. I’ll have to try some of your suggestions out next time I’m waiting around at a doctor’s office or something. Thanks!
Liz Elias (author) from Oakley, CA on December 09, 2012:
Bill Holland from Olympia, WA on December 09, 2012:
Great ideas; a couple of them I have played, but a couple I have never heard of. I’ll keep these in mind for the holiday parties that are sure to come.
Power Pivot And Power Bi: The Excel User’S Guide To Dax, Power Query, Power Bi &Amp; Power Pivot In Excel 2010
Microsoft Power BI, including Power Pivot and Power Query, are a set of free add-ons to Excel that allow users to produce new kinds of reports and analyses that were simply impossible before.
This book, printed in full-gorgeous color, gives you an overview of Power BI, Power Pivot and Power Query, and then dives into DAX formulas, the core capability of Power Pivot. Always from the perspective of the Excel audience.
Written by the world’s foremost Power BI bloggers and practitioners, the book’s concepts and approach are introduced in a simple, step-by-step manner tailored to the learning style of Excel users everywhere. The techniques presented allow users to produce, in hours or even minutes, results that formerly would have taken entire teams weeks or months to produce.
This book includes lessons on:-
difference between calculated columns and measures
how formulas can be reused across reports of completely different shapes
how to merge disjointed sets of data into unified reports
how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not
how to create time-intelligent calculations in pivot tables such as “Year over Year” and “Moving Averages” whether they use a standard, fiscal, or a complete custom calendar.
how to leverage Power Query to make your Power Pivot models awesome!
how to use Power BI Desktop and chúng tôi and how they fit into the Excel landscape
The “pattern-like” techniques and best practices contained in this book have been developed and refined over several years of onsite training with Excel users around the world, and the key lessons from those seminars costing thousands of dollars per day are now available to you, within the pages of this easy-to-follow guide. This updated second edition covers new features introduced with Office 2016 and Power BI Desktop.
At PowerPivotPro, I have really enjoyed working with the Power Pivot and Power BI community. This book, with Rob Collie, was an effort to reach out and help an even greater audience. Little did I know what I was signing up for 🙂
It was hard juggling book writing with the training/consulting/travel. But I am glad we persevered. Big thanks to our IndieGoGo crowdfunding supporters, who not only made it possible to print this book in full-gorgeous color, but also inspired us to burn the night oil in writing those last chapters.
I sincerely believe that Power Pivot and Power BI can transform the lives of Excel users worldwide – after all I was one of them. But change is hard, even when it’s for the better. We hope we can help you go from Excel to Power BI with this book and our services at PowerPivotPro.
Power On! -Avi Singh
Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data. We have repeatedly witnessed the “Power tools” transformative impact on the bottom line – far more robust and elegant than Excel alone, and much more agile and affordable than traditional BI tools. Their shared state of the art calculation engines (DAX and M) turn any PC into an analytical supercomputer, but the real secret is people – these tools are 100% learnable by today’s users of Excel. The data revolution lays not in the hands of an elite few, but in YOUR hands, and we want to help you seize that opportunity.
Rob Collie is an entrepreneur, author, and consultant. He was one of the founding engineers behind Power Pivot at Microsoft and operates the world’s leading Power Pivot/Power BI website, chúng tôi He lives in Indianapolis. Avichal Singh (Avi) is Principal Consultant at PowerPivotPro where he blogs, trains and consults on PowerPivot and Power BI. His past experience includes building large scale Power BI solutions at Microsoft. He lives in Seattle.
How To Install Power Query
Power query is a great tool built by Microsoft that will help you work with data in Excel. This tool is great for connecting to various external data sources, querying and transforming data, or cleaning and parsing data.
Web pages, Facebook
Excel, CSV, XML, Text or Hadoop (HDFS) Files
Various databases like MS Access, SQL Server, MySQL, Microsoft Azure SQL, Oracle, IBM DB2, PostgreSQL, Sybase, Teradata, OData etc…
This is available as an add-in for excel 2010 professional plus or 2013 and comes already built in for Excel 2016.
You can download Excel Power Query here from Microsoft.
Unfortunately, if you’re not running Excel 2010 professional plus or 2013, then you will need to upgrade to Excel 2016 in order to use this feature as it’s not available for previous versions of Excel. Mac user are also out of luck.
There are both a 32-bit and 64-bit versions and which one you choose will depend on the version of Excel which you have installed.
To check what version you have:
Go to the “File” tab.
Go to the “Help” section.
Here you will see the product version, if it says professional plus 2010, then you’re in luck.
Here you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.
To check what version you have:
Go to the “File” tab.
Go to the “Account” section.
Here you will see the product version.
In the screen that pops up, at the top you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.
Power query comes pre-installed in Excel 2016 but has been renamed to “Get & Transform” and is under the Data tab in the ribbon. If you have Excel 2016, then you don’t need to do anything to use it.
Download The Add-In
Go to the Microsoft website:
Select your preferred language.
Select The Correct Version
Select either the 32-bit or 64-bit version depending on your version of Excel.
Run The Setup Wizard
Follow The Setup
Follow the steps in the Setup Wizard.
Power Query Is Now Ready To Use
Now the next time you open up Excel, Power Query will be available to use under its own tab.
Cập nhật thông tin chi tiết về Join Two Or More Tables In Excel With Power Query trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!