Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Power BI and Power Query in Excel

Microsoft Power BI

Microsoft Power BI (business intelligence) is a collection of online services and features that enable 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 information 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 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 sheets, 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 the 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 tab and do the following:

1. Select 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 below)

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

The 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 command Close & Load It
Load Table from sheet into Power Query Editor

7. You opened a new dialog box. Select "Only create connection"
The connection table in 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
The connected table in 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 the 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
Connecting two queries within the Microsoft Power Query for Excel

3.Opened a dialogue box where you drop down menu to select the first two queries (the first two tables) and click OK.
Connecting two queries or two tables in Micrtosoft Power Query for Excel

4. Appears a new window "Query Editor". Immediately rename the name of Append1 into the name that you will be convenient to remember. I renamed into "source data". 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 command "Append Queries". If you have more then them in this step you need to add to the first merged request.

Adding queries on the existing queries into Microsoft Query Power for Excel

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.

Add a new table to an existing Power Query

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 It".

Save loaded query into Query Editor window

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

Connecting table through Query Editor into Power Query for Excel

Table created with Power Query into Excel

9. And finally, you notice the newly created table "source data" 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.