Mynda Treacy – Power Query for Excel

SalePage: Mynda Treacy – Power Query for Excel

Description of Power Query for Excel

Do you spend time each month cleaning and transforming data into the right format before you can even begin to analyse it or incorporate it into your reports?

Do you perform repetitive data cleansing tasks like:

• Deleting rows, columns, or blanks
• Filtering out data you don’t need
• Fixing the layout so it will work in a PivotTable
• Splitting or joining columns or text
• Removing spaces and special characters from your data
• Merging worksheets, text/CSV files together
• Extracting data from an external database
• Incorporating the next month’s data to an existing report
• ….and all the other laborious repetitive tasks that put you to sleep and give you RSI

Tasks you imagine macros could do in seconds, if only you knew how. Or maybe you have some macros that handle it all but keeping the VBA code up to date is constant task and no one else knows how to update it.

The Good News

If you have Excel 2010, or later, you can use Power Query to automate the tasks of:

1. Getting your data – from almost anywhere
2. Cleaning and reformatting your data – so it works in PivotTables etc.
3. Updating your reports – Power Query will update new or existing Tables, PivotTables or Power Pivot models

You see once you show Power Query where to find your data and how you want it cleaned and reformatted (which is dead easy), it will remember the steps so next time you can simply hit the Refresh button and it’ll do it automatically.

And this means you can get on with the fun part of analysing the data and impressing your boss so you can go home early.

And the best part is there’s NO VBA or programming required.

In fact you’ll be up and running faster than it would take you to learn the equivalent formulas required to perform the same tasks.

What is Power Query?

Power Query is a free add-in built by Microsoft for Excel users like you and I. It’s available in all Desktop versions of Excel 2010 and 2013.

And in Excel 2016, 2019, 2021 and Office 365 it’s built right into the Data tab in the Get and Transform group:

It’s also available in Power BI Desktop.

What does Power Query do?

1. Power Query gets your data from almost anywhere – the web, databases, Excel files, Facebook, OData, SharePoint, Salesforce etc. and the list keeps on growing with new data sources being added all the time.

2. Power Query enables you to clean, transform, merge and append data with a point and click style GUI that’s easier to learn than formulas or VBA.

3. Load the data into Excel and or Power Pivot and consume it in your analysis, reports or export elsewhere.

Readmore about: Mynda Treacy