Skip to main content

Power Query - Excel's most powerful tool


Power Query cleans and transforms data

Power Query Process Overview Steps - Transform Data
  1. You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
  2. Press buttons in the Power Query Editor window to transform your data.
  3. Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.

Power Query is like a machine because once you have your query setup, the process can be repeated with the click of a button (refresh) every time your data changes.

If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding.

Common Data Tasks Made Easy

Do you work with data that has been exported from a system of record?  This could be a general ledger, accounting, ERP, CRM, Salesforce.com, or any reporting system that contains data.

If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.

Unpivot Exported Data with Power Query in Excel

These data transformations could include tasks like:

  • Remove columns, rows, blanks
  • Convert data types – text, numbers, dates
  • Split or merge columns
  • Sort & filter columns
  • Add calculated columns
  • Aggregate or summarize data
  • Find & replace text
  • Unpivot data to use for pivot tables

Do any of these tasks sound familiar?  If so, then they probably also sound boring, repetitive, and time consuming. 🙂  Believe me, I’ve spent the better part of my career doing these tasks and trying to figure out faster ways to get them done.

Fortunately, Power Query has buttons that automate all these tasks!

Overview of the Power Query Ribbon

Starting in Excel 2016 for Windows, Power Query has been fully integrated into Excel.  It is now on the Data tab of the Ribbon in the Get & Transform group.

Power Query Get and Transform Data Tab of Ribbon Excel 2016 2019 Office 365 Microsoft 365

In Excel 2010 and 2013 for Windows, Power Query is a free add-in.  Once installed, the Power Query tab will be visible in the Excel Ribbon.

Power Query Ribbon for Excel v2.16.3785.242
Click to Enlarge

You use the buttons in the Data or Power Query tab to get your source data.  Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, websites, and more.

Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.

Power Query Editor Window Home Tab Transform Data with Buttons

The buttons in the Power Query Editor Window allow you to transform your data.

Power Query Editor Window Home and Transform Tab Buttons

Think about some of those tasks you do repeatedly as you browse the buttons in the image above.  Each time you press a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data by refreshing the query.

After completing your steps, you can output the data to a Table in your Excel workbook by clicking the Close & Load button.

Close and Load Button in Power Query Editor Window

You can also modify existing queries and refresh your output tables with the changes or updated data.

Data Transformation Examples

Here are a few examples of what Power Query can do with your data.

Unpivot Data for Pivot Tables

My favorite feature of Power Query is it’s ability to Unpivot data.  This is a technique used to get your data ready for the source of a pivot table.  This is also referred to as normalizing your data to get it in a tabular format.

The data might start out looking something like the following.

Exported Report Data in ExcelFile

And you want the end result to look like this.

Unpivot Data with Power Query for use in Pivot Table

Power Query can do this with the click of a few buttons, and prepare your data for use in a pivot table.

Labels:

Comments

Post a Comment

Popular posts from this blog

Charts - Make your data presentable

One-click charts are easy: Select the data and press  Alt+F1 . What if you would rather create bar charts instead of the default clustered column chart? To make your life easier, you can change the default chart type. Store your favorite chart settings in a template and then teach Excel to produce your favorite chart in response to  Alt+F1 . Say that you want to clean up the chart above. All of those zeros on the left axis take up a lot of space without adding value. Double-click those numbers and change Display Units from None to Millions. To move the legend to the top, click the + sign next to the chart, choose the arrow to the right of Legend, and choose Top. Change the color scheme to something that works with your company colors. Right-click the chart and choose Save As Template. Then, give the template a name. (I called mine ClusteredColumn.) Select a chart. In the Design tab of the Ribbon, choose Change Chart Type. Click on the Templates folder to see the template that ...
Post a Comment
Read more

Data Analysis Tool Pack

  The  Analysis ToolPak  is an  Excel add-in  program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click on OK. 4. On the Data tab, in the Analysis group, you can now click on  Data Analysis . The following dialog box below appears. 5. For example, select Histogram and click OK to create a Histogram in Excel. Example Rank and Percentile The Rank and Percentile contained within the Analysis-ToolPak can be quickly used to find the rank of all the values in a list. The advantage of using the Rank and Percentile feature is that the percentile is also added to the output table. The percentile is a percentage that indicates the proportion of the list which is below a given number. Highlight the list (or the cells) which...
Post a Comment
Read more

20 Power BI Dax Measures

Power bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples: Total Sales: scss Copy code Total Sales = SUM (Sales[Amount]) Average Sales Price: scss Copy code Avg Sales Price = AVERAGE (Sales[Amount]) Total Units Sold: mathematica Copy code Total Units Sold = SUM ( Sales [ Quantity ] ) Total Customers: scss Copy code Total Customers = COUNTROWS (Customer) Total Products: mathematica Copy code Total Products = COUNTROWS ( Product ) Maximum Sales Amount: scss Copy code Max Sales Amount = MAX (Sales[Amount]) Minimum Sales Amount: scss Copy code Min Sales Amount = MIN (Sales[Amount]) Sales Growth Percentage: mathematica Copy code Sales Growth % = ( Total Sales - [ Total Sales Last Year ] ) / [ Total Sales Last Year ] Total Profit: scss Copy code Total Profit = SUM (Sales[Profit]) Total Orders: scss Copy code Total Orders = COUNTROWS (Orders) Total Customers with Sales: css Copy code Total Customers with Sales = COUNTROWS( FILTER ...
Post a Comment
Read more