RESOURCES

Some tools to make life easier. You’re welcome :)

• beehiiv - my choice for a newsletter operating system

• Coefficient - Live connections to 60+ business systems

• Lido - Automate your spreadsheets; accurately extract date from PDFs

• TransactionPro - quickly import, export or delete data in Quickbooks

• Perspective - Build high-converting lead funnel microsites

• Gamma - AI designer for presentations, websites, social posts, and more

• Gratitude Plus - social gratitude journal

• Senja - collect and display testimonials

• OpusClip - turn long videos into short clips

• Carrd - free one-page website builder

• Notion - notetaking + project management + database

• Transistor - my favorite podcast host

• Fathom - AI-powered notetaking app

MAIN ARTICLE

Power Query basics: handling comma-separated values

I don’t usually break out Power Query. But it’s a great tool that can bypass having to write a bunch of nested formulas, and I want to show you the basics with a simple example.

I started with a data set that had several comma-separated values inside of individual cells. You can clean this up with formulas, but we’ll do it with Power Query.

The 4-Step Power Query Solution

This isn't an advanced technique, it's basic Power Query functionality.

Here's how to do it:

Step 1: Get Your Data Into Power Query

Go to your Data tab and click Get Data > From Table/Range.

This opens the Power Query editor with your data loaded. You'll see all your columns, but we're only interested in the one with comma-separated values.

Step 2: Split the Column by Delimiter

In the Home tab, find Split Column > By Delimiter.

Power Query will probably detect that you want to split by comma.

Click Advanced Options and change it from "Split into columns" to "Split into rows."

Step 3: Clean Up the Spacing

After splitting, you'll notice some entries have spaces in front of them. That's because your original data probably looked like "Crime, Drama" with a space after the comma.

Go to Transform > Format > Trim. This removes those leading and trailing spaces.

Step 4: Group and Count

Back to the Home tab, click Group By.

Make sure your genre column (or whatever you're counting) is selected. Name the new column something like "Count" and set the operation to Count Rows.

Click OK.

That's it. Power Query now shows you each unique value and how many times it appears in your data.

Getting Results Back to Excel

To get this back into your spreadsheet, click Close & Load.

You can choose where to put the results—maybe in a new table next to your original data.

The nice thing about Power Query is that it creates a connection. If your source data changes, you can refresh this query and get updated counts automatically.

Why This Matters

This technique works for any comma-separated data:

• Customer tags from a CRM

• Survey response categories

• Product features

• Skills listed on resumes

It's faster than manual parsing and more reliable than trying to build complex formulas.

Plus, once you understand this basic Power Query workflow, you can build on it for more complex data transformations.

What's Next?

If you prefer using formulas instead of Power Query, I covered that approach in a previous video. Both methods work—choose whatever feels more comfortable for your workflow.

What are you dealing with? Reply and let me know what problems you’re running into. I might cover your specific use case in a future newsletter.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.

4. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.