Blogs

Hey Reporting and Analytics folks,

In the midst of preparing for a data storytelling workshop I came across the need to create a frequency table, which is basically a way to organize data into categories and show how often each category appears. It helps to quickly see patterns, trends, and distributions in a dataset. Initially, I thought about using Chat-GPT or Microsoft Copilot to come up with a quick solution since I probably would only need to do this once and won't be refreshing that data. However, I wanted to provide my learners with a solution that they could take back with them and apply it to their own data projects. Since I am using Microsoft Excel as my data analysis, prep, and visualization tool - excellent in terms of ubiquity and accessibility as it's widely available and has a low barrier to entry - I decided to take advantage of a very powerful functionality known as Power Query. To access this robust transformation functionality, check out the Data tab and specifically the Get & Transform and Queries & Connections options in the ribbon. Here are the steps I took to create a frequency calculation and the resulting table of values:

Start with the data:
Below are the compiled responses for my survey question, "What factors most influenced your current career path?" Note that respondents could select multiple answers.

Set your Goal: Count how many times each individual reason (like "Personal passion", "Academic strengths", etc.) appears, weighted by the count from the "Responses" column.

Go through the Steps:

Enjoy the End Result: frequency table

What do you think of this technique? Are you using Excel's Query functionality to transform data?