Power Query – The Heavyweight Champ of Power BI

Part 2 of a 5 part series

What is Power Query?

Power Query is a self-service data extraction tool that is a free add-in for Excel 2010 or higher. It allows users that are already comfortable with Excel a smaller learning curve to start enjoying Power Query. Power Query has a vast array of options that it can use as data sources. The types of sources that can be used are referenced in the screen shot.


Extracting a Source Data

One of the key tenets of Self-Service BI is that business users should be able to get the data they need to gain insights into their businesses in a timely manner. With Power Query, business users who use Excel can immediately begin extracting, transforming, and combining disparate sets of source data into meaningful information and knowledge to help them gain important insights about their businesses. To connect to a SQL data source follow this steps:

1. Click on the Power Query in Excel and then on the “From Database” button and select From SQL Server Database.


2. Supply SQL Server and database connection and then select the table(s) would want to work with from the Navigator pane.


3. Select Patron database and then “Edit” button.


Shaping your Data

Loading source data is only part of the puzzle, because in most cases your source data needs to be transformed or shaped differently to make it consumable. Typical transformation tasks include but are not limited to formatting record keys from different data sources into a single format; splitting data that is combined in a single column into multiple columns; and decoding cryptic column names using dictionary files to give the column values meaning to business users.

4. Transform/cleanse data by clicking replace values and filling in find and replace data.


5. Click on Split Column and select Custom delimiter from drop down and “-” to split the Zip column.


6. Replace Values to clean up values in a selected column

a. In the Query Editor ribbon, click Replace Values or right-click the column and click Replace Values to replace data.


7. Naming the query

a. In the Query Settings pane, in the Name text box, enter your name to define the query. This is also where you can modified the Applied Steps to the original data.


8. After you have cleansed, load the query to a worksheet

a. In the Query Editor ribbon, in the Query group, click Apply & Close.


Power Query for Excel almost effortlessly handles most data transformation tasks and quickly makes your data consumable. What’s especially powerful is the way that Power Query implements its data transformation functionality. For every change you make to a dataset, from simple tasks such as renaming columns or replacing values in strings, to traditionally more complex tasks such as splitting a single column into multiple columns or un-pivoting a large number of columns with cryptic names so they can be decoded, a macro is automatically created and executed to implement the change. If you make a mistake and want to undo a change, you simply delete the macro from the query, and the change will be undone.


Power Query is an excellent tool in Microsoft’s Power BI Suite and the heavyweight champ when extracting and shaping the data that business users need in a timely manner for additional analysis and visualization.

Power BI – Kick Start Your Company’s Self Service BI

Part 1 of a 5 part series

Power BI is, in my opinion, one of the most significance advances that Microsoft has made in the area of Business Intelligence in the cloud, and it significantly advances their offering in personal self-service Business Intelligence arena. To start with, Power BI requires Excel 2013 and is made up of four components. Additional features of the Power BI offering from Microsoft include Cloud-Based Self Service BI through an Office 365 site.

Four Components to Power BI

Two of the Power BI components are for data preparation:  Power Pivot and Power Query.  The other two are for data visualization:  Power View and Power Map. 

  • Power Pivot.  Imports and integrates data from various sources for the purpose of creating an in-memory data model.  This allows a functional user to combine various data sources which improves its value – classic examples here are the addition of weather data or demographics data to corporate sales data.  The Power Pivot data model also supports calculations, derived fields, aggregates, hierarchies and key performance indicators.  The ability to create a data model with a relatively low learning curve is a very big deal for data analysts, particularly for one-time analysis type of situations.  It can also be helpful to IT and BI folks for prototyping.
  • Power Query.  One of the killer features of Power Query is the online search functionality to get at public data like Wikipedia, US Census data. This tool is able to search for data (just like a web search experience) online or within your corporate data sources.  The selected data set is immediately imported into an Excel table – this search feature alone is a game-changer.  It also allows you to do things like merge data, rename columns, replace values and other data modifications in a step-by-step process.  A very big deal here is that the step-by-step data cleansing and shaping process is savable and therefore repeatable.  You can also view the state of the data before and after each step.  Optionally, you can bring the data into your Power Pivot model to continue working with the data even further.
  • Power View.  Power View is an interactive data visualization tool.  It does certain things like highlighting and cross-filtering really well with very little learning curve.  Working with data in Power View bears similarities to working with Excel Pivot Tables as well as to PowerPoint.  In addition to charts, graphs, and table data, Power View also supports maps which pan and zoom via integration with Bing Maps.  The naturally interactive behavior of Power View is what makes it a very big deal.
  • Power Map.  Power Map is a 3D mapping tool.  What Power Map does is allow you to take a data model built with Excel and/or Power Pivot, and plot the data on the surface of a 3D map. The map can then be rotated and zoomed, and animations created around different views or time dimensions of the data.

Mobile Power BI

The cloud-based Power BI is delivered through an Office 365 site (which actually uses SharePoint Online). The Office 365 site will include mobile support through native mobile applications for Windows 8, Windows RT and iPad. HTML5 will be used for browser-based mobile delivery on other types of devices.  Since the data can exist both on premises, or in the public, how does a cloud based service refresh data from inside your firewall? This will be handled through the Office 365 Admin Center by the creation of a Data Management Gateway and then scheduling of the refreshes of the data.

To help company’s get started with Power BI, I will be posting a five part series on the Power BI tools available for Dynamics GP and other ERP users and how you can use this tools to provide quick access to data to make the best decisions possible. Some of these tools include: Business Analyzer, SQL Server Reporting Services (SSRS), Dynamics GP Excel Reports and Jet Express for GP. We will use some of these tools in this blog series to:

  • Part 2 – Power Query – How to use it.
  • Part 3 – Power Pivot – Creating your first data model and KPIs.
  • Part 4 – Power View and Power Map – Analyzing data through visualization.
  • Part 5 – Office 365 site – Making my Power BI spreadsheet mobile with Data Management Gateway data refreshes.