Select Page

Data Shaping with Microsoft Excel Power Query

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.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

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

CONTACT US

Work for Us

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

EXPLORE JOBS