Blue Flower

How To Work With 'Power Bi' and 'Power Query' in Excel

Microsoft Excel Power BI

Microsoft Power BI (business intelligence) is a collection of online services and features that enables you to find and visualize data, share discoveries, and collaborate in intuitive new ways. There are two experiences now available for Power BI: the new experience, generally referred to as Power Bi, and the previous experience which is referred to as Power Bi for Office 365.

The new experience is centered on PowerBi.com, an online service where you can quickly create dashboards, share reports, and directly connect to (and incorporate) all the data that’s important to you. The new experience also introduces the Power BI Desktop, a dedicated report authoring tool that enables you to transform data, create powerful reports and visualizations, and easily publish to Power Bi. The new experience extends to all your mobile devices, too. For more details and informations visit web page Microsoft Power BI.

Introduction to Microsoft Power Query for Excel

Power Query is a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

The Power Query add-in enhances Excel by providing a comprehensive interface for querying a wide range of data sources. It can also be used to perform data enhancements such as cleansing data by replacing values, and combining data sets from different sources. Power Query includes a data source provider for HDInsight, which enables users to browse the folders in Azure blob storage that are associated with an HDInsight cluster. You can download the Power Query add-in from the Office website.

Combine Data from Multiple Data Sources (Power Query)

On the Microsoft website Combine data from multiple sources Power Query, you can see step by step how it looks to work with Power Query in Excel. I will in this tutorial try to show a simple example of how we can use Power Query to merge multiple Excel tables or merge data from multiple worksheets.

How to Activate or Enable the Power Query for Excel 2013

To use the Power Query in Excel 2013 Add-Ins it is necessary to do the following:

  • Download Power Query from the Microsoft website
  • Install by running * .msi files
  • Run MS Excel 2013 and load Power Query to Excel
  • Restart Excel 2013

 Enabling Add-Ins Power Query in Excel

The ranges of data on multiple worksheets, which we need to merge to a single table

In this Excel example tutorial a simple example I will show you how using Power Query, we can merge data from multiple tables from multiple worksheets. Of course the actions mergers data from multiple worksheets, we can do this in several ways (using formulas, Pivot Table from Multiple Worksheets, etc.) But our goal also be using Power Query.

In the pictures below you see three worksheets, where there are ranges of data identical headers.

Worksheet "jan"

 Data on the first worksheet in Excel

Worksheet "feb" 

 Data on the second worksheet in Excel

Worksheet "mar" 

 Data on the third worksheet in Excel

 

Converting Range of Data Into Excel Table

You notice in the pictures above, that all the data is in a range of cells. So the data presented are not Excel spreadsheets (Table). About differences between the range of data and actual Excel tables I wrote the tutorial Design Table in Excel.

To be able to use data from the respective worksheets, you need to respective ranges of data converted into Excel spreadsheets (Table). So Position the active worksheet "jan" and on the ribbon to jump to the INSERT Toolbar tab and do the following:

  1. Select the all range of data with a header in the first row
  2. Click on the Table
  3. Check My table has headers
  4. Click on OK (see picture bellow)

 Converting the range of cells that contains a header into Excel spreadsheet

Previous action, repeat on each worksheet. You now have on the three worksheets Excel table (Table1, Table2, Table3) you can see on the Formulas tab => Name Manager. Style and color, you can choose from the Design tab Table Tools

 Converted ranges of data into Excel table or spreadsheet

Creating connections Table1, Table2 and Table3 in Workbook Queries for Power Query

In the previous steps we performed preliminary work, to be able to go multiple merge data from multiple worksheets on the a single worksheet. The next step is to create a connection of each table (Table1, Table2, Table3) in Power Query. This addition Table into Power Query will do in the following way.

1. Position on the first worksheet "Jan".

2. Activate the Power Query tab

3. Select the one cell on inside Table1

4. Click on the command From Table

 Loading Table1 in Power Query

5. You opened the Query Editor with the data presented in Table1

6. Click on the Close & Load It comman.

 query editor close to load table data

7. You opened a new dialog box. Select "Only create connection"

create connection table to power query

8. And finally, our Excel window Power Query looks like as shown below. You note in right part of the "Workbook Queries pane" that Table1 the connected.

connected table to power query

This action above in all the above mentioned steps, you make on each worksheet (february and march). Ultimately, you need to have three tables in the connected "Workbook Queries pane" as shown below.

workbook queries and connected tables

Merge data from multiple Excel table or from multiple worksheets

Navigate to worksheet "master". On the master worksheet, we will create a new table on the which all data from multiple worksheets to be merged into one data range. Here you can filter all data according to certain criteria, perform a variety of calculations and the like. To connect multiple tables from multiple worksheets using Power Query, do the following:

1. You position the worksheet on the "master" and select the cell 'A1'

2. Click the Append command to connect the first two queries

append data in power query

3. Opened a dialogue box where you drop down menu to select the first two queries (the first two tables) and click OK.

append two queries in power query

4. Appears a new window "Query Editor". Immediately rename the name of Append1 into name that you will be convenient to remember. I renamed into "sourceData". In this step, we have combined two queries into one. Let's add a third request, ie, connected to the previous one.

Adding multiple queries and connect to the previous queries in Microsoft Query Power for Excel

5. While still open window "Query Editor" Click the "Append Queries" command. If you have more then them in this step you need to add to the first merged request.

append multiple queries in power query

6. You opened a dialog box in which the drop down menu, select the following table you want to add and merge with the already previous query.

append new query table

7. Here you can do some settings such as changing the type of display (Data Type). Selecting an entire column => Data Type, and set a specific type of data presentation. Notice in the image that is necessary to change the type of data display for the column "Values".

The next step into "Query Editor" window, click on the "Close & Load" button, and continue to click on "Close & Load To...".

append query settings

8. Showed up the familiar dialog box, where do the following. Select the "Table" and "Existing worksheet". Click the Load button.

append table cross query editor in power query

Table created with Power Query into Excel

9. And finally, you notice the newly created table "sourceData" containing merged data from multiple tables from multiple worksheets. In the picture below you will see that they are loaded with 33 rows.

Also you notice that I am the first column filtered by specific names. You must display format for the column 'D' into "Currency".

 Jointed data more tables through Power Query

This is just one example of how we can use Microsoft Power Query for Excel. You have to do it study and improve yourself knowledge using numerous tutorials on the Internet and YouTube.