Excel Super-Powers

It seems Microsoft has been inspired by Marvel by creating functionality called POWER

It has Power Query, Power Pivot & Power BI

All 3 have the same basis, combining functionality of databases & working with big data sets

  • Power BI is a standalone package that imports spreadsheet data

This data can be manipulated, cleaned & calculations added

From there data visualisations can be created in a dashboard style

Great for reporting top level insights to director/manager level in a company

With the ability to update the base data in Excel & refresh on a regular basis

  • Power Query & Power Pivot are both within Excel & found under menu Data -> Get & Transform Data

They are linked & the data can be imported off the spreadsheet you are working on or from other files including .csv & .txt

The Power Query does similar to BI in that it cleanses data or combines into one table

This can be downloaded into Excel after or just sit as a query for later

Power Pivot uses these queries to create pivot tables directly off the queries without seeing the data in any sheet

All 3 have the advantage of manipulating data without using formulas & summarising afterwards

Plus, there is no restriction on the rows

A big advantage over trying to manipulate the data within Excel sheet as the more data, the slower everything gets & the file size gets very big quickly

Files with Power Query stay much smaller as long as you keep the data within the query

Very easy to adjust the raw data & add more data

Chishiki

knowledge, understanding, information
Excel Tips

When to use =sumif()

There are a couple of ways to add up your dataThe obvious one is =sum()The other is adding up individual cells, handy if there are

Read »

Kaigi o tehai shimashou

let's arrange a meeting