Tech Tips

  • Tech Tips
  • 07.28.20

Exploring Data Using Excel’s Power Query Editor

  • by Marcia A. Finch, Senior Institutional Research Analyst, Neumann University

While I primarily use Microsoft Excel’s Power Query Editor to put together a complicated table or report in a short period of time, I’ve learned that you can also use it to explore any data set. Here are the steps you can take, and what features are available.

From the Data tab of Excel, click Data --> Launch Power Query Editor. This can be done in any spreadsheet.

Launch Power Query Editor Example

Using the Home tab in the Editor, select New Source --> Excel to pick the file and spreadsheet (tab) you wish to explore. This will be at the far right of the ribbon.

New Excel source example

Here’s an example of what you will see when selecting your file of interest. Click OK to continue.

File of interest example

Go to the View tab. Click the checkboxes “Column distribution,” “Column profile,” and “Column quality” to enable the data summaries.

Enable data summaries example

Column statistics and Value distributions will change based on the column you have selected. The above picture shows a summary provided for a text variable. For a numeric variable you get even more information, such as counts for Not a Number (NaN), Zero, Average, Standard Deviation, and Even or Odd. The three dots next to Column statistics will allow you to copy that table of data. The three dots next to Value distribution allow you to group your data by data values, sign (positive/negative), or parity (even/odd); and you can also filter the data. Unfortunately, there is no way to sort the value distribution in numerical order at this time.

Column statistics and Value distributions example

When you’re done, you can close and discard changes if you have no need to continue working with the data set in Power Query. Feel free to contact me with any questions!