Đề Xuất 3/2023 # How To Combine Tables With Power Query # Top 4 Like

Cập nhật nội dung chi tiết về How To Combine Tables With Power Query mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Bottom Line: Learn how to combine tables in Excel using Power Query.

Skill Level: Intermediate

Video Tutorial

Download the Excel File

If 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 Tables

If 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 #1

All 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 #2

The 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 Query

Now 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 Tables

To 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 Macro

Since 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 Settings

If 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 Append

Once 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 Data

This 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 Tables

If 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.

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

A Folder

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.

Excel 2010

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.

Excel 2013

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.

Excel 2016

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.

Conditional Merge In Power Query

In this example I will use the WideWorldImportersDW sample database Microsoft offers. You should have a copy of SQL Server installed to play with for Power BI. The Developer version of SQL Server is 100% functional and free forever. You just cannot use it in production.

I am going to use two tables for this example, Fact.Sales and Dim.Customer. I only want my Fact Sales table to have customer info for the Buying Group Tailspin Toys. That info is in the Dim Customer table. I can do this several ways.

Do the merge, expand the Buying Group column from the Customer table, then filter to only show those rows.

Pre-filter the Customer table for Tailspin Toys in the Buying Group column, then do the merge. It would need to be an Inner Join, otherwise you will get nulls in the Sales table when you expand, and then you have to filter those out. Which you can do, but it is more steps.

You can bypass all of that and do it in one step.

I’m not going to bore you with how to do methods 1 and 2. Let’s do method 3, a conditional join!

First, create the merge as you normally would. In the Sales table, select Merge Queries from the ribbon, then select the Customer table. Select the Customer Key fields in both. Also make sure this is an inner join so it will remove all records from the Sale Table that are not Tailspin Toys. Your Merge dialog box will look like this:

Table.NestedJoin has the following parameters per MS documentation:

We want to tweak 3rd parameter – table2, which is our Customer table, We don’t want the full table2, we only want table2, filtered for Topspin Toys in the Buying Group field.

So in the code above, we need to replace the 3rd parameter which is just a reference to #”Dimension Customer” – the name of the Customer Table. (It is just Dimension Customer – the #”name here” syntax is just how Power Query references objects with spaces and special characters.)

We want to replace that with this code:

Table.SelectRows(#”Dimension Customer”, each [Buying Group] = “Tailspin Toys”)

Since 100% of the work is done on the SQL Server, it will be very fast compared to how it would run if the Power Query mashup engine on your PC or On-Premise Gateway would process it.

If you are using some other source, like text files or Excel files, folding won’t happen of course, but it should still perform well as this is single filter applied, not a filter applied for every row of either table.

I cannot say that this method is any faster than doing it the longer ways (methods #1 and #2 above) but it is shorter code. Plus, you can get fancier with the Table.SelectRows() function we used by filtering on multiple fields using and/or criteria. You may break folding if you get too fancy and the Power Query engine cannot figure the SQL out, so be careful. If performance is a must, methods #1 or #2 are more likely to fold with many filters in the condition, but you won’t know until you try. Happy Querying!

The Complete Guide To Power Query

What is power query?

Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed.

It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA.

The best part about it, is you don’t need to learn or use any code to do any of it. The power query editor records all your transformations step by step and converts them into the M code for you, similar to how the Macro recorder with VBA.

If you want to edit or write your own M code, you certainly can, but you definitely don’t need to.

Get the data used in this post to follow along.

What Can Power Query Do?

Every month you need to go to the folder where the file is uploaded and open the file and copy the contents into Excel.

You then use the text to column feature to split out the data into new columns.

The system only outputs the sales person’s ID, so you need to add a new column to the data and use a VLOOKUP to get the salesperson associated with each ID. Then you need to summarize the sales by salesperson and calculate the commission to pay out.

You also need to link the product ID to the product category but only the first 4 digits of the product code relate to the product category. You create another column using the LEFT function to get the first 4 digits of the product code, then use a VLOOKUP on this to get the product category. Now you can summarize the data by category.

Maybe it only takes an hour a month to do, but it’s pretty mindless work that’s not enjoyable and takes away from time you can actually spend analyzing the data and producing meaningful insight.

Where is Power Query?

Power Query is available as an add-in to download and install for Excel 2010 and 2013 and will appear as a new tab in the ribbon labelled Power Query. In 2016 it was renamed to Get & Transform and appears in the Data tab without the need to install any add-in.

Importing Your Data with Power Query

Get data from a single file such as an Excel workbook, Text or CSV file, XML and JSON files. You can also import multiple files from within a given folder.

Get data from various databases such as SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata and SAP HANA databases.

Get data from Microsoft Azure

Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.

Get data from other sources like a table or range inside the current workbook, from the web, a Microsoft Query, Hadoop, OData feed, ODBC and OLEDB.

We can merge two queries together similar to joining two queries in SQL.

We can append a query to another query similar to a union of two queries in SQL.

Note: The available data connection options will depend on your version of Excel.

Depending on which type of data connection you choose, Excel will guide you through the connection set up and there might be several options to select during the process.

A Simple Example of Importing Data in an Excel File

This will open a file picker menu where you can navigate to the file you want to import. Select the file and press the Import button.

When you’re satisfied that you’ve got everything you need from the workbook, you can either press the Edit or Load buttons. The edit button will take you to the query editor where you can transform your data before loading it. Pressing the load button will load the data into tables in new sheets in the workbook.

In this simple example, we will bypass the editor and go straight to loading the data into Excel. Press the small arrow next to the Load button to access the Load To options. This will give you a few more loading options.

The Query Editor

The Ribbon – The user interface for the editor is quite similar to Excel and uses a visual ribbon style command center. It organizes data transformation commands and other power query options into 5 main tabs.

Query List – This area lists all the queries in the current workbook. You can navigate to any query from this area to begin editing it.

Formula Bar – This is where you can see and edit the M code of the current transformation step. Each transformation you make on your data is recorded and appears as a step in the applied steps area.

Properties – This is where you can name your query. When you close and load the query to an Excel table, power query will create a table with the same name as its source query if the table name isn’t already taken. The query name is also how the M code will reference this query if we need to query it in another query.

Applied Steps – This area is a chronological list of all the transformation steps that have been applied to the data. You can move through the steps here and view the changes in the data preview area. You can also delete, modify or reorder any steps in the query here.

The Query List

The Query List has other abilities other than just listing out all the current workbook’s queries.

When you do eventually exit the editor with the close and load button, changes in all the queries you edited will be saved.

Copy and Paste – Copy and paste a query to make another copy of it.

Delete – Delete the query. If you accidentally delete a query, there’s no undo button, but you can exit the query editor without saving via close and load to restore your query.

Rename – Rename your query. This is the same as renaming it from the properties section on the left hand side of the editor.

Duplicate – Make another copy of the query. This is the same as copy and paste but turns the process into one step.

Move To Group – Place your queries into a folder like structure to keep them organised when the list gets large.

Move Up and Move Down – Rearrange the order your queries appear in the list or within the folder groups to add to your organisational efforts. This can also be done by dragging and dropping the query to a new location.

Create Function – Turn your query into a query function. They allow you to pass a parameter to the query and return results based on the parameter passed.

Convert To Parameter – Allows you to convert parameters to queries or queries to parameters.

Properties – Allows you to change the query name, add a description text and enable Fast Data Load option for the query.

The Data Preview

The main job of the data preview area is to apply transformation steps to your data and show a preview of these steps you’re applying.

You can choose from decimal numbers, currency, whole numbers, percentages, date and time, dates, times, timezone, duration, text, Boolean, and binary.

The Applied Steps

You can insert new steps into the query at any point by selecting the previous step and then creating the transformation in the data preview. Power Query will then ask if you want to insert this new step. Careful though, as this may break the following steps that refer to something you changed.

Edit Settings – This allows you to edit the settings of the step similar to using the gear icon on the right hand side of the step.

Rename – This allows you to rename the steps label. Instead of the displaying the generic name like “Filtered Rows“, you could have this display something like “Filtered Product Rows on Pens” so you can easily identify what the step is doing.

Delete – This deletes the current step similar to the X on the left hand side of the step.

Delete Until End – This allows you to delete the current step plus all steps up until the end. Since steps can depend on previous steps, deleting all steps after a step is a good way to avoid any errors.

Insert Step After – This allows you to insert a new step after the current step.

Move Up and Move Down – This allows you to rearrange the query steps similar to the dragging and dropping method.

Extract Previous – This can be a really useful option. It allows you to create a new copy of the query up to the selected step.

The Formula Bar

The File Tab

Close & Load – This will save your queries and load your current query into an Excel table in the workbook.

Close & Load To – This will open the Import Data menu with various data loading options to choose from.

Discard & Close – This will discard any changes you made to the queries during your session in the editor and close the editor.

Note, you will still need to save the workbook in the regular way to keep any changes to queries if you close the workbook.

Data Loading Options

You can choose to load the query to a table, pivot table, pivot chart or only create a connection for the query. The connection only option will mean there is no data output to the workbook, but you can still use this query in other queries. This is a good option if the query is an intermediate step in a data transformation process.

You’ll also be able to select the location to load to in your workbook if you selected either a table, pivot table or pivot chart in the previous section. You can choose a cell in an existing worksheet or load it to a new sheet that Excel will create for you automatically.

The other option you get is the Add this data to the Data Model. This will allow you to use the data output in Power Pivot and use other Data Model functionality like building relationships between tables. The Data Model Excel’s new efficient way of storing and using large amounts of data.

The Queries & Connections Window

Data Preview – This is a live preview of the data similar to when first setting up a query.

Last Refreshed – This will tell you when the data was last refreshed.

Load Status – This displays whether the data is loaded to a table, pivot table, pivot chart or is a connection only.

Data Sources – This will show you the source of the data along with a count of the number of files if you’re it’s a from folder query.

The Home Tab

Close – You can access the Close & Load and Close & Load To options from here. These are also available in the File tab menu.

Manage Columns – You can navigate to specific columns and choose to keep or remove columns.

Reduce Rows – You can manage the rows of data from this section. There are lots of options to either keep certain rows or remove certain rows. Keep or remove the top N rows, the bottom N rows, a particular range of rows, alternating rows, duplicate rows or rows with errors. One option only available for removing rows is to remove blank rows.

Sort – You can sort any column in either ascending or descending order.

Transform – This section contains a mix of useful transformation options.

Split Columns – This allows you to split the data in a column based on a delimiter or character length.

Group By – This allows you to group and summarize your data similar to a Group By in SQL.

Data Type – This allows you to change the data type of any column.

Use First Row as Headers – This allows you to promote the first row of data to column headings or demote the column headings to a row of data.

Replace Values – This allows you to find and replace any value from a column.

Combine – This sections contains all the commands for joining your query to with other queries. You can merge, append queries or combine files when working with a from folder query.

Parameters – Power Query allows you to create parameters for your queries. For example when setting up a from folder query, you may want the folder path to be a parameter as so you can easily change the location. You can create and manage existing parameters from this section.

Data Sources – This section contains the data source settings including permissions management for any data sources that require passwords to access.

New Query – You can create new queries from new data sources or previously used data sources from this section.

The Difference Between the Transform and Add Column Tabs

The bulk of all transformations available in power query can be accessed through either the Transform tab or the Add Column tab.

You might think there is a lot of duplication between these two tabs. For example, both tabs contain a From Text section with a lot of the same commands. It’s not really the case, there is a subtle difference!

When you use a command from the Add Column tab that is found in both tabs, it will create a new column with the transformed data and the original column will stay intact. Whereas using the equivalent command from the Transform tab will change the original column and no new column is created.

This is a critical point to be aware of!

The Transform Tab

Table – This section contains commands that will transform the entire table. You can group and aggregate your query, promote rows to headers, demote headers to rows, transpose your data, reverse row order, and count rows.

Any Column – This section contains commands that will work on any column of data regardless of data type. You can change the data type, automatically detect and change the data type, rename the column heading, find and replace values, fill values down (or up) a column to replace any blanks or nulls with the value above it (or below it), pivot or unpivot columns, move columns to a new location or convert a column to a list.

Text Column – This section contains commands for text data. You can split columns with a delimiter, format the case, trim and clean, merge two or more columns together, extract text, and parse XML or JSON objects.

Number Column – This section contains commands for numerical data. You can perform various aggregations like sums and averages, perform standard algebra operations or trigonometry and round numbers up or down.

Date & Time Column – This section contains commands for date and time data. You can extract information from your dates, times and duration data.

Structured Column – This section contains commands for working with nested data structures such as when your column contains tables.

The Add Column Tab

General – This section allows you to add new columns based on formulas or custom functions. You can also add index colummns or duplicate a column from here.

From Text – Very similar to the From Text section in the Transform tab, but these commands will create a new column with the transformation.

From Number – Very similar to the From Number section in the Transform tab, but these commands will create a new column with the transformation.

From Date & Time – Very similar to the From Date & Time section in the Transform tab, but these commands will create a new column with the transformation.

The View Tab

Layout – This section allows you to either show or hide the Query Setting pane (which contain the properties and applied steps) and the Formula Bar.

Data Preview – This section allows you to show or hide whitespace characters or turn the font into a monospace font in the data preview area. This is handy when dealing with data delimited by a certain number of characters.

Columns – This allows you to go to and select a certain column in the data preview. This command is also available in the Home tab.

Parameters – This allows you to enable parameterization in data sources and transformation steps.

Dependencies – This will open a diagram view of the query dependencies in the workbook.

In particular, the Query Dependencies view is a useful resource that allows you to see a visual representation of the data transformation process flow.

Conclusions

Power Query can seem overwhelming at first to someone new to it all, but the UI is very well laid out and easy to catch on to. While it might be new to a user, a lot of the concepts should be familiar to an Excel user already.

Getting familiar with all the parts of the editor and the layout of the ribbon tabs is an essential first step in exploring Power Query and incorporating it into your everyday work.

While there is a lot to learn about Power Query, it is worth putting in the time to learn. There is massive potential to save time in repetitive data cleaning and formatting tasks with it. It’s one of the most powerful and useful tools that has been added to Excel since pivot tables.

Want even more power query goodness? Then check out these Amazing Power Query Tips to help you get the most out of it!

Bạn đang đọc nội dung bài viết How To Combine Tables With Power Query trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!