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

Data Model an easy substitute for Vlookup

  you have a data set with product, date, customer, and sales information. The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector. Time for a VLOOKUP, right? There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or newer. These versions of Excel have incorporated the Power Pivot engine into the core Excel. (You could also do this by using the Power Pivot add-in for Excel 2010, but there are a few extra steps.) In both the original data set and the lookup table, use Home, Format as Table. On the Table Tools tab, rename the table from Table1 to something meaningful. I’ve used Data and Sectors. Select one cell in the data table. Choose Insert, Pivot Table. Starting in Excel 2013, there is an extra box, Add This Data to the Data Model, that you should select before clicking OK. The Pivot Table Fields list appears, with the fields from the Data table. Choose Revenue. Because you are using the Data Model, a new line a...
Post a Comment
Read more

Update All Sheets at same time in a Excel Workbook

Say you have to urgently submit a file with many sheets. For Example put a Header or write a same  data for all sheets. I will show you an amazingly powerful tool called Group mode. Say that you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on any worksheet tab and choose Select All Sheets. The name of the workbook in the title bar now indicates that you are in Group mode. Anything you do to the January worksheet will now happen to all the sheets in the workbook. Why is this dangerous? If you get distracted and forget that you are in Group mode, you might start entering January data and overwriting data on the 11 other worksheets! When you are done adding totals, don’t forget to right-click a sheet tab and choose Ungroup Sheets.
Post a Comment
Read more

Improve your Excel Productivity with these Shortcuts and formulas

I have given below 45 tips and tricks to improve your productivity while working in Excel. Useful Keyboard Shortcuts 1.  To format any selected object , press ctrl+1 2.  To insert current date , press ctrl+; 3.  To insert current time , press ctrl+shift+; 4.  To repeat last action , press F4 5.  To edit a cell comment , press shift + F2 6.  To autosum selected cells , press alt + = 7.  To see the suggest drop-down in a cell , press alt + down arrow 8.  To enter multiple lines in a cell , press alt+enter 9.  To insert a new sheet , press shift + F11 10.  To edit active cell , press F2 (places cursor in the end) 11.  To hide current row , press ctrl+9 12.  To hide current column , press ctrl+0 13.  To unhide rows in selected range , press ctrl+shift+9 14.  To unhide columns in selected range , press ctrl+shift+0 15.  To recalculate formulas , press F9 16.  To select data in current region , press ctrl+shift+8 ...
Post a Comment
Read more