How to Import, Edit, Load and Consolidate Data in Excel Power Query?

Power Query is an easy and efficient way of solving simple data tasks. Most of our valuable time is frequently consumed by tedious manual procedures like cut and paste, column merging, and filtering. These operations are greatly simplified with the Power Query tool.

A further advantage is that, in comparison to other BI tools, Power Query is simple to use. The Power Query user interface is straightforward. Given how much it resembles the Excel interface, many users will find it to be familiar.

What is Power Query?

Power Query is an application for preparing and transforming data. Using Power Query, you may perform transformations to data obtained from sources using a Power Query Editor and a graphical user interface. 

We can import data from a variety of sources, clean it, convert it, and then reshape it to suit our needs using Power Query, a business intelligence tool provided by Microsoft Excel. Power Query allows us to create a query just once and reuse it later by simply refreshing it.

Different Ways to Import Data to the Excel Sheet

Excel allows importing data from a variety of sources. We will look at each of these in detail. While importing data, power query has several features and options that we can utilize.

Import data from the table

• Open the Excel file and go to any cell.
• Go to the Data tab on the top of the ribbon, then select from the table.
• It will open the “create table” dialogue box.

 

• Give the range of data sources. Select ok.

 

Import data from the File

We can import data from a variety of files viz. workbook, CSV, XML, text, PDF, or folder. To import data from the file existing in the system, follow the following steps:

• Go to the Data tab on the top of the ribbon and then to the Get external data group.
• Select “from file”.
• Select any of the file type where the data source is located.

 

• For example, click on “from workbook”.
• Select the exact file having data and click Import.
• Select the table source that you want to load the data from.
• Click on load.
• Data from the file will be visualized in tabular form in the current Excel sheet. A similar process applies to another type (CSV, text, XML, etc.) of file selection. 

Import data from the Web

• Go to the Data tab on the top of the ribbon and then to the Get external data group.
• Select “from web”.

 

• In the “New web query” dialogue box, type the URL link that has the data source in the address bar.
• Click on Go. It will load the page.

 

• Click on import.
• It will load the whole table in the current Excel file.

Import data from the Database

• Go to the Data tab on the top of the ribbon, then to the Get external data group.
• Click on “from database”.

 

• Select from “SQL server database”.
• It will prompt a dialogue box to import data from the SQL server database.
• Enter the server’s name and click ok.

 

• In the access Microsoft SQL database dialogue box, check on “use my current credentials” and click on connect.
• It will load the database in the current spreadsheet.

 

Result: Once the importing process is completed, Excel will:

• Load the data in the existing worksheet by generating a table.
• Display the pane of “Queries & Connections”. This will show the query we have created.

How to Edit a Power Query

Power Query provides a variety of features to edit a query in the Excel workbook. In the “query options” window, we can set default settings. We can also determine if the data loaded was from a power query by checking the “Query” tab in the main ribbon. To edit a query with the power query in the workbook, follow the steps:

• Go to the Data tab on the top of the ribbon, then to the Get External Data group.
• Select From File and then click on From Workbook.

 

• Identify the workbook that has a data source and click on import.

 

• In the navigator box, select the table/ sheet that contains the data.
• Click on Edit.

 

• It will open the query editor where we can edit the query.

 

• Once done, click on close & load.

 

• It will load the complete dataset in the current workbook, including the changes made so far.
• Similar steps apply when loading and editing the data with other data sources, viz from the database, from the web, from other files(text/CSV/XML), or from other data sources.

Example: We will edit a column to change its data type from numbers to date.

 

• Click on the column whose data type needs to be changed.
• Right-click on the column date and then choose change type and select date.

 

• Select “replace current”. Here we will replace the existing conversion. We can also add new conversions at this step.

Other ways to edit a query

Edit Query from Data

• To edit a query from data, locate previously loaded from the Power Query Editor.
• Then select a cell in the data and go to Query and then click Edit.

Edit from the Queries & Connections Pane

When there are multiple queries in a workbook, the queries pane may prove to be a more convenient option.

• Go to Data and then choose Queries & Connections.
• Go to the Queries tab.
• Locate the query in the list of queries, right-click on it, and then select Edit.

Edit from the Query Properties Box

• Go to Data and then choose Data & Connections, then select the Queries tab.
• Right-click the query you want to edit.
• Select Properties. It will open a dialogue box.
• Select the Definition tab in the dialogue box.
• Select Edit Query.

 

Query editing allows you to edit the query before completing the import process. Editing allows to

• Determine and transform the source data column to work with.
• Perform the modifications/ editions required to clean, and shape the data into the required transformation.

Result:

• Load the data in the existing worksheet by generating a table.
• Display the pane of “Queries & Connections”. This will show the query we have created.

Excel Power Query: Load Data

Power query helps to specify how and where the data we import is loaded. There are 2 different ways to apply the settings of the data loading:

• The Query Editor.
• The Navigator dialogue box or the dialogue box is named after the source (when working with a CSV or text file).

To load the query, follow the steps:

• Go to data and then in Get external data group.
• Select from the file and then choose CSV file. (Or any other source we want)

 

• Double-click on the file that has the data source. Click Import.

 

• It will open a navigator dialogue box.
• Select the item that has the data tables, and it will open a preview in the panel.
• Click on “load to”.

 

• In the dialogue box, we want to create only an existing connection, so check mark the button.
• Click “load”.

 

• In the workbook queries pane, a connection is established as shown in the figure below.

 

• To load the data from the established connection, right-click on the connection. Select “load to”.

 

• It will open load to dialogue box. (As shown in previous steps)
• Click on table to load the data set in the existing workbook.

Result:

• Load the data in the existing worksheet by generating a table.
• Display the pane of “Queries & Connections”. This will show the query we have created.

Excel power query: Combine the data

There are two different ways to combine the data imported from different data file sources:

• Append data to existing table/query
• Create new one

Consolidate Data by Creating a New Query

• Click on Data and then select the new query option.
• Select combine queries and then append.

 

• It will open the append dialogue box. In the first panel, select the main table in which data needs to be added. In the second table, select the one that needs to be appended with the primary one. Click OK.

 

• It will open the query editor dialogue box. Click on “close & load”.

 

• It will load the table in the current sheet and in the queries panel, the appended rows will be displayed.

 

Consolidate Data by Appending it to the Existing Query

• To append to the existing query, select the data table or the connection in the queries pane. Right-click on the query you want to combine. Click on append.

 

• It will open the append dialogue box with the first selection set itself. Enter the second table name you want to append.

 

• Click OK. It will open a query editor containing modified rows in the appended table. Click on close & load.

 

• This will load the existing rows in the appended query.

Result:

• Load the data in the existing worksheet by generating a table.
• Display the pane of “Queries & Connections”. This will show the query we have created.