Bạn đang xem bài viết Power Query Overview: An Introduction To Excel’S Most Powerful Data Tool đượ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.
Bottom line: Learn how this awesome feature of Excel and Power BI called Power Query will help you automate the process of importing, transforming, and cleansing your data to save a TON of time with your job.
Skill level: Beginner
Video Tutorial Download the Sample FilesThe CSV files I use in the video are available for download below. You will need to extract the files out of the zip file.
Introduction to Power QueryIn this tutorial I provide an introductory explanation of Power Query. You will learn why this is my new favorite Excel tool for working with data, and how it can help automate processes and save you time!
The Power Query Data MachineI was watching a TV show on how things are made, and they were explaining how a depositor machine worked in a pastry factory.
The basics of a depositor machine are:
You add ingredients to it.
Change some settings.
And it magically creates pastries (cookies, donuts, biscuits) that are ready for baking.
Once the dials are set, the process can be repeated over-and-over again to make perfect pastries every time. Getting hungry…? 🙂
You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
Press buttons in the Power Query Editor window to transform your data.
Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.
If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding.
Common Data Tasks Made EasyDo you work with data that has been exported from a system of record? This could be a general ledger, accounting, ERP, CRM, chúng tôi or any reporting system that contains data.
If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.
These data transformations could include tasks like:
Remove columns, rows, blanks
Convert data types – text, numbers, dates
Split or merge columns
Sort & filter columns
Add calculated columns
Aggregate or summarize data
Find & replace text
Unpivot data to use for pivot tables
Do any of these tasks sound familiar? If so, then they probably also sound boring, repetitive, and time consuming. 🙂 Believe me, I’ve spent the better part of my career doing these tasks and trying to figure out faster ways to get them done.
Fortunately, Power Query has buttons that automate all these tasks!
Overview of the Power Query RibbonStarting in Excel 2023 for Windows, Power Query has been fully integrated into Excel. It is now on the Data tab of the Ribbon in the Get & Transform group.
In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will be visible in the Excel Ribbon.
You use the buttons in the Data or Power Query tab to get your source data. Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, chúng tôi Dynamics CRM, Facebook, Wikipedia, websites, and more.
Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.
The buttons in the Power Query Editor Window allow you to transform your data.
Think about some of those tasks you do repeatedly as you browse the buttons in the image above. Each time you press a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data by refreshing the query.
You can also modify existing queries and refresh your output tables with the changes or updated data.
Data Transformation ExamplesHere are a few examples of what Power Query can do with your data.
Unpivot Data for Pivot TablesMy favorite feature of Power Query is it’s ability to Unpivot data. This is a technique used to get your data ready for the source of a pivot table. This is also referred to as normalizing your data to get it in a tabular format.
The data might start out looking something like the following.
And you want the end result to look like this.
Here is an article and video on exactly How to Unpivot Your Data with Power Query.
Checkout my article on how to structure your source data for a pivot table if you are unfamiliar with why your data needs to look like this for a pivot table.
Append (Combine) Tables with Power QueryThe Append feature of Power Query allows you to combine multiple tables (stack them vertically) to create one large table. It can do this with multiple tables in one file, or it can pull in data from a bunch of different files/sources.
Let’s say you have a folder that contains CSV or Excel files with report data for each month. Throw all those files in the Power Query machine, and it will spit out one nice table that you can then use to create pivot tables and charts.
If the data in those reports also needs to be transformed (remove rows, split columns, unpivot, etc.), then Power Query can handle that in the same process.
Once it is setup, all you have to do is hit the refresh button every month when a new file is added to the folder and the rows will be added to your output table.
How awesome is that! 🙂
Merge Tables – A VLOOKUP AlternativePower Query has the ability to merge or join tables. This can be used as an alternative to VLOOKUP or INDEX/MATCH formulas.
Let’s say you have this data table of sales records, and you are using a VLOOKUP to bring in information about the product based on the name of the product sold. Your product group information is located in another table on a different sheet or workbook.
Using VLOOKUP formulas is great, but it can often mean adding thousands of formulas to your workbook. Which increases the file size and calculation time.
Create Custom FunctionsHowever, Power Query can be programmed to create custom functions. This gives you seemingly unlimited potential to transform your data in just about any way possible.
It is based on the M language, and most of the functions are very similar to writing a formula in Excel. This also makes it more user friendly and easier to learn the code.
This new language and set of functions means there is a lot to learn, but I consider that the fun and challenging part. Plus, employers of the future will definitely be looking for employees with Power Query skills.
Power Query Records Your Steps & Automates ProcessesPower Query not only makes all these tasks easier, but it also records your steps so you do NOT have to do them over-and-over again. It will save you a lot of time if you are preparing the same data every day, week, or month.
It also does a pretty good job of handling errors. If the structure of your source data changes, Power Query will tell you what step it broke at and allow you to fix it. This makes maintenance easy and you don’t have to completely redo your process when something changes.
You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports. This is a critical step in the process of summarizing and analyzing data.
The Power Query Machine & Power BIWell, it can’t exactly make cookies, but Power Query is a pretty awesome tool! It will save you a ton of time when transforming your data.
Power Query is just one piece of the suite of Power BI (Business Intelligence) products from Microsoft.
If we go back to the analogy of baking cookies in a factory, you can think of Power Query as the first step in the assembly line. Once the cookies are formed, we then need to bake them (Pivot Tables, PowerPivot) and then package them for presentation (Power View, Power Map, Charts, Dashboards, etc.)
How Do I Get Power Query?The other nice part is that Power Query is now built into Excel starting with Excel 2023 for Windows. If you are on Excel 2010 or 2013 then Power Query is a free add-in.
I have a dedicated page that will help you determine if you have the right version of Excel to get Power Query. It also provides complete installation instructions and the download link.
Complete Guide to Installing Power Query
To give you an idea of the importance of this tool, Power Query was fully integrated into Excel in Excel 2023 for Windows, and is on the Data tab of the Ribbon.
It is also known as Get & Transform, although the term Power Query is most common.
Additional ResourcesThis article has provided an overview of the basics of Power Query that should help you understand some of the major features. Power Query has a ton of features and there is definitely a lot to learn.
I will be sharing more how-to articles and videos in the coming weeks. Here are a few resources that will help you get started.
How to Unpivot Your Data with Power Query + Video Tutorial
Free Training Webinar on the Power ToolsRight now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
If not, do you think it would be useful for you? Are you doing any of the tasks I mentioned manually right now?
I will be creating more how-to articles and videos on Power Query in the future, so I’m interested to know what you want to learn.
Thank you! 🙂
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 2023 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.
Join Two Or More Tables In Excel With Power Query
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 QueryIn simple terms, Power Query (also known as Get & Transform in Excel 2023 and Excel 2023) 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 2023 and Excel 2023, 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 2023.
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.
Source dataAs 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 connectionsNot 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 tableWith 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 tableAt 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 ExcelWith 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 ExcelNow 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.
How To Combine Tables With Power Query
Bottom Line: Learn how to combine tables in Excel using Power Query.
Skill Level: Intermediate
Video Tutorial Download the Excel FileIf you’d like to download the file that I use in the video, you can do so here:
Here is the file that contains the VBA macro to create Power Query connections to all tables in the workbook.
Combining TablesIf you have tables on several worksheets that contain the same type of data and you are looking to combine them into one master table, Power Query can help you do it quickly and effectively. This is a great alternative to copying and pasting data piece by piece, which can get tedious if there are several tables that you want to merge.
There are just two prerequisites to keep in mind.
Prerequisite #1All of the sheets or data sets that you are looking to combine must be formatted as Excel Tables, not just data set up in a table format.
To turn a data set into an Excel Table, just select any cell in the set and then choose Format as Table on the Home tab. It’s usually a good idea to name the table after you’ve created/inserted it.
If you are relatively new to Excel Tables, check out my Beginner’s Guide to Excel Tables. Here are some useful Tips & Shortcuts for Inserting Excel Tables, and this post will give you some Best Practices for Naming Excel Tables.
Prerequisite #2The tables you are working with must contain the same column headings, though they do not have to be in the same order. If you are working with columns that have similar data, but your headings are not the same, Power Query will put them into different columns when it combines them.
There are ways around this, which I will cover in a future post.
The Setup Work in Power QueryNow you can create queries in Power Query. First we will create connection queries for each table. Then we will combine those queries with an Append query to combine or stack the data.
1. Create Connection Queries to the TablesTo combine, or append, your tables together, you need to create a connection to each of them in Power Query.
This brings up a preview of your data. To create a connection:
Select Close & Load To…
That brings up the Import Data window. From here, select Only Create Connection.
You can see the connection you’ve just created in the Queries & Connections pane. If ever you don’t see the Queries & Connections pane, you can open it by selecting that button on the Data tab in the ribbon.
This process of creating connections must be repeated for every table that you want to append. Again, you only need to do this work one time for the initial setup. However, here are a few tips to speed up the process.
Use the Table Connections MacroSince creating and connecting lots of tables can be time-consuming, I’ve created a macro that automates it. The macro loops through all tables in the workbook and creates connection only queries for any table that do not have queries yet.
I will write a post in the future that explains the macro. However, you can download the file that contains the VBA macro code here.
The macros runs on the Active Workbook. You can add the macro to your Personal Macro Workbook and add a macro button to the Ribbon or Quick Access Toolbar to run it on any open workbook.
Close & Load SettingsIf you’d rather not use a macro, you can also shorten the process by changing the setting of the Close & Load split-button. The default for the top half of that button will load the output table to a new sheet, but you can adjust the settings so that it only creates a connection instead. To change the setting:
Go to the File menu.
Select Options and Settings.
Choose Query Options.
That will bring up the Query Options window, where you can select Specify custom default load settings.
Deselect the Load to worksheet option.
Hit OK.
Just remember to change this setting back once you’ve finished connecting all of your tables.
2. Combining Connected Tables with AppendOnce all of your tables are connected, it’s a piece of cake to consolidate them:
This brings up the Append window, where we can select Three or more tables. This allows us to move any or all of the tables that we’ve connected from our Available tables (on the left) to the list of Tables to append (on the right).
Once you hit OK, you will be taken back to the Power Query editor, where you can see a preview of the combined tables. You can make adjustments and transformations to the data before closing the editor and loading the data to a new worksheet.
Updating & Refreshing the DataThis means we have fully automated this process. You do NOT have repeat the steps above every time your data changes or you get new rows in your tables.
Adding New TablesIf you ever want to add new tables to the query (or exlcude existing ones) you can reopen the Append window by:
Opening the Query Settings pane if it’s not already visible (View tab, then Query Settings).
This opens the Append window, where you can add or delete tables.
The new columns will still need to have the same column header name on each sheet. If any of the tables are missing columns, then Power Query will fill the rows for that table with blank (null) values in the append query and output table.
Other Power Query PostsIf you’re just getting started with Power Query, check out my overview post here: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool.
Then get Power Query up and running with this tutorial: The Complete Guide to Installing Power Query.
Free Training Webinar on the Power ToolsRight now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
Cập nhật thông tin chi tiết về Power Query Overview: An Introduction To Excel’S Most Powerful Data Tool 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!