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!!!

Microsoft Power Query 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:

Power Query For Excel 13

Once we have downloaded it we run the executable file in order to proceed with the installation. Then the Microsoft Power Query 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.

Power Query Wizard

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

License Terms

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

Destination Folder

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.

Ready To Install

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

Installing Power Query

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.

Power Query Wizard

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.

Power Query

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.

Get External Data

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

From File

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

From 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

From Other Sources

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.

Recent Sources

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.

Excel Data

Combine:

  • Merge: We can merge two queries from this workbook.
  • Append: We can append two queries from this workbook.

Combine

Manage:

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

Manage

Workbook Settings:

  • Locale: The Locale determines the regional settings used to interpret numbers, dates, and time in imported text for this workbook.
  • Fast Combine: It controls whether Power Query combines data from two different data sources securely.

Workbook Settings

Machine Settings:

  • Data Source Settings: We can manage the settings for our data.
  • Update: We can install the latest version of Power Query.
  • Options: We can define options for our Power Query environment.

Machine Settings

Power BI:

  • My Data Catalog Queries: We can view the list of queries that we have sent to the Power BI Data Catalog.
  • Sign In: We can sing in with our OrgId to access shared queries and search for data.

Power BI

Help:

  • Send Feedback: We can send feedback whether we liked or not Power Query.
  • Help: We can access Power Query Help content.
  • About: We can learn more about Power Query.

Help

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

Frow Web

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.

Power Query Tab

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.

Select Multiple Items

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.

Navigator Results

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.

Navigator

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

Workbook Queries Task Pane

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

Workbook Queries

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.

Query Results

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.

Query Editor

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

Power Query For Excel 13

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

Posted on October 9, 2014, in Excel English 2013, Microsoft Office 2013 English and tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , . Bookmark the permalink. Comments Off on Power Query For Excel 13.

Comments are closed.