Data Shaping with Microsoft Excel Power Query

Anyone 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.