Select Page

Data Shaping with Microsoft Excel Power Query

Author: Eric Russo | | June 23, 2015

data_shapeAnyone who works with data probably has some comfort working with Microsoft Excel. The software’s tabular presentation of data, combined with its ability to easily export, import, and sort, gives it the core capabilities of data analysis. For those who want to take their analytics up a notch, Microsoft introduced several new “Power” features with Excel 2013: Power Pivot, Power View, Power Maps, Power BI, and Power Query.

Filtering and Shaping Data with Excel Power Query Editor

Using Excel’s new Power Query editor, you can select a number of variables to use in shaping your data. A preview pane allows you to see the impact of your choices on a small sample without having to run the Power Query on the entire database. You can tune the Power Query and instantly see changes in the preview pane.

In the Power Query editor, you can also choose to hide specified columns, pivot on columns, filter or sort the columns. A formula bar offers common data-shaping functions such as mode, median, average, standard deviation, and so forth. These can be embedded into a Power Query that includes calls to other databases and multiple rounds of importing and exporting. The step pane in Excel Power Query Editor allows you to see and tweak the step-by-step progression of these functions in Power Query.

Power Queries can be created, tested, modified, and then stitched together into longer strings. Because of the immense amount of data shuffling required to shape large databases, some precautions need to be taken. For example, you can turn off the load to worksheet option when your intention is to export the shaped data sets into an Excel data model.

Due to the ease of testing Power Queries without altering the database, and building Power Queries from smaller fragments, it’s easy to troubleshoot fragments of the Power Query without having to scrap the entire script and start over.

Excel Power Query Options and Settings

One of Datvail’s new white papers, Self Service BI: Excel Power Query, continues with some of the options that are available once you get comfortable with Power Query. These include:

  • PowerBI – a business intelligence option that allows sharing Power Queries
  • Azure – imports from Azure Marketplace, Azure HDInsight, and other Azure datasets
  • Pivoting – allows you to quickly reshape data from the way you received it to the way you want it

These and other capabilities of Excel Power Query are explained in the whitepaper. As the largest pure-play database services provider in North America we help clients just like you to stay abreast of the latest tools and technology available to help make everyday tasks easier. We have around-the-clock database teams who can run the numbers to generate the kind of useful data you need for decision making. Please contact us today to discuss what Datavail can do to make your life easier.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Oracle Database Extended Support Deadlines: What You Need to Know

If you’re confused about Oracle’s extended support deadlines, you are not alone. Here’s an overview of what’s in store for 11g through 19c.

Steve Thompson | January 19, 2021

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.