Power Query For Excel 13

Have you ever wanted to identify the data that we need from other sources? Have you ever wanted to discover relevant data, to combine data from multiple sources, to disparate it and to prepare the data for further analysis? Have you ever wanted to share queries for collaboration with other users?

This is where Microsoft Power Query appears!!!

MicrosoftPowerQuery for Excel is an Excel add-in that provides a seamless experience for data discovery, data transformation, enrichment and sharing for Information Workers, BI professionals and other users.

In order to download it and to install it you must follow the link below:

Once we have downloaded it we run the executable file in order to proceed with the installation. Then the MicrosoftPowerQuery for Excel Setup dialog box appears welcoming us and all we have to do is to press the Next button, which is located at the bottom right corner of the window.

The next step in order to proceed is to accept the terms in the License Agreement and then to press again the Next button.

In the next step of the wizard, we can change the DestinationFolder of where the PowerQuery will be installed. All we have to do is to press the Change button and select a different DestinationFolder. One way or the other we press the Next button once again in order to proceed.

Once we have done all the above in the next step of the Microsoft Power Query For Excel Setup we press the Install button which is located at the bottom of the window.

In the next step of the wizard we can see the Status of the installation as shown in the image below.

Finally we have Completed The Microsoft Power Query For Excel Setup Wizard as shown in the image below and all we have to do now is to press the Finish button.

When we start Excel, the next time we will notice a new tab, which is named Power Query as, shown below which is separated by the following areas on the ribbon.

Get External Data:

Online Search: We can discover and import data based on a search.

From Web: We can import data from a Web page.

From File:We can import data from a file.

From Database: We can import data from a database.

From Other Sources:We can import data from other sources.

Recent Sources: We can manage and connect sources.

In the image below I have selected to Get External Data From File. Once selected the drop down menu appears where we can Get External Data from the following:

From Excel

From CSV (Comma Separated Value)

From XML (Extensible Markup Language)

From Text

From Folder

In the image below I have selected to Get External Data From A Database. Once selected, the drop down menu appears where we can Get External Data from the following:

From SQL Server Database

From Windows Azure SQL Database

From Access Database

From Oracle Database

From IBM DB2 Database

From MySQL Database

From PostgreSQL Database

From Sybase Database

From Teradata Database

In the image below I have selected to Get External Data From Other Sources. Once selected the drop down menu appears where we can Get External Data from the following:

From SharePoint List

From OData Feed

From Windows Azure Marketplace

From Hadoop File (HDFS)

From Windows Azure HDInsight

From Windows Azure Blob Storage

From Windows Azure Table Storage

From Active Directory

From Microsoft Exchange

From Facebook

In the image below I have selected to Get External Data From Recent Sources. Once selected the drop down menu appears where we can Get External Data from the most recent sources that we visited.

Excel Data:

From Table: We can create a new query that is linked to the selected Excel table or named range. If the selected range is not part of a table or named range, it will be converted automatically into a table.

Combine:

Merge: We can merge two queries from this workbook.

Append: We can append two queries from this workbook.

Manage:

Workbook Queries: We can view the list of queries in this workbook.

Workbook Settings:

Locale: The Locale determines the regional settings used to interpret numbers, dates, and time in imported text for this workbook.

My Data Catalog Queries: We can view the list of queries that we have sent to thePower BI Data Catalog.

Sign In: We can sing in with our OrgIdto access shared queries and search for data.

Help:

Send Feedback: We can send feedback whether we liked or not PowerQuery.

Help: We can access Power Query Help content.

About: We can learn more about PowerQuery.

In the image below I have selected to Get External Data From Web. Straight away theFromWeb dialog box appears where I have entered the Web Page URL, into the text box, and then I pressed the OK button.

At the right of the Excel window the Navigator task pane appears, where we can see the address that we typed before, inserted in the task pane.

Once we press the arrow located at the left of the Web pages address, all the associated items with the specific page appears below the address in the Navigator task pane.

If we hover over with our mouse over the items that are in the Navigator task pane, we can see a preview of the Results of the specific item as shown in the image below.

In the image below I have activated the check boxes next to the items that I want to load. At the bottom of the Navigator task pane, I have select the command Load and from the shortcut menu that appears we can select either the command Load or the command Load To. If we select the command Load the items will be inserted in the active workbook. If we select the command Load To, we must define in which workbook the items will be inserted.

Once selected all we have to do is to wait for the WorkbookQueries to be inserted in the workbook.

In the image below we can see the data that has been inserted in our Workbook.

When we hover over the data in the Navigator task pane, as mentioned above a preview of the data that will be inserted will appear at the right as shown in the image below. In this preview of the Results, at the bottom of the window we can either select one of the following commands:

View In Worksheet: The data will be inserted in our worksheet.

Edit: The Query Editor window will appear where we will be able to edit the query.

Send To Data Catalog: Send the data to the Data Catalog.

Delete: Delete completely the data.

In the image below, I have selected to edit the query and straight away, the Query Editor window appears where we can do the adjustments that we want.

Below you can check out the video describing Power Query for Excel 13.

Power Query For Excel 13

Related

About officesmart

MVP:
Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide.
Microsoft Master Specialist:
This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system