3 Power Query Commands and HACKS

Power Query is the solution to messy data problems and is the best data cleaning tool Excel has to offer (fight me if you disagree).

For those unfamiliar with this tool, it's definitely worth adding to your Excel learning goals this year.

If you're learning (or improving) your Power Query skills, try out these 3 Power Query commands and my personal HACKS 💡

1. Transpose

I’ve never had a dataset that was exactly right. For whatever reason, the shape of the data is all wrong. I end up using the transpose feature perhaps more than any other. By simplifying flipping your data from rows-to-columns, you can change so much.

2. File from Folder

File from Folder is a game changer. You can use one file as your “template.” Then whatever you render onto the file is rendered onto an entire set of like-files in a folder. In the last 5 years of consulting, I have used this connector more than any other. You can see a File from Folder example in my Advanced Excel Course.

3. Column from Example

Column from Example uses machine learning to predict how you want to transform your data. I often call it the Macro recorder for Power Query. Column from Example is a great way to learn how Power Query thinks - and to become more familiar with Power Query commands and what they do.

With Power Query, let’s make dirty data a thing of the past.