Power Query and Power Pivot

If you've been paying attention lately, you'll have noticed that Excel is evolving rapidly! In the last couple of years Microsoft have added new tools like Power Query and Power Pivot, just to name a few.  These are amazing new tools, and it can be really easy to get comfortable with the tools that you already know without realising that there are tonnes of new tools and features designed to get the job done in a fraction of the time. 

Embracing these new tools will not only enable you to get your work done more quickly, but also since less than 1% of Excel users know these tools you're going to have a huge competitive edge in the workplace!

It's easy to ignore these developments and continue doing things the way you always have, but if you ever find yourself thinking:

• "My Excel reports take way too long to update each month/week"
• "I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what's the point?"
• "If I could just impress the boss I'd get that pay rise/promotion I deserve"
• "I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants"
• "I'm not sure how to approach setting up my Excel workbook the right way, so it's easy to build and maintain"

then you really should consider spending some time on learning these new tools. If you really want to dazzle your boss and colleagues, then here are some of the new tools you need to know:

Power Query for Excel

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, 2013* and 2016.

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.

Power Pivot for Excel

Are you sick of waiting for the IT department to extract data from your systems?

Do you spend too much time flattening your data tables into one with VLOOKUP just so you can use it in a PivotTable report?

Are you frustrated that Excel can’t handle more data?

Power Pivot for Excel fixes all that.

Big Data on your Desktop

Leveraging “big data” in your business might be crunching sales patterns to plan inventory levels, cutting staff expenses by analysing employee overtime and rearranging work schedules or using social media data to gauge what customers think about your business and competitors.

Power Pivot for Excel enables you to transform enormous quantities of data with incredible speed into meaningful information to get the answers you need in seconds, all with the comfort of familiar Excel tools and features.

• Import millions of rows of data from virtually any source including relational databases, data feeds, Reporting Services, text files, multidimensional sources, cloud services, data feeds, Excel files, and data from the Web into a single Excel workbook,
• Create relationships between the different sources just like Access.
• Build interactive reports with PivotTables, PivotCharts and Slicers, and then further analyze the data so that you can make timely business decisions.
• Power Pivot calculations and analysis are extremely fast. You can process millions of rows in about the same time as thousands.
• Data Analysis Expressions (DAX) is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis like never before.
• The DAX formula language is very similar to that of Excel formulas.

All without requiring IT assistance.