Copy
Power BI - Insights from LInkedIn - Pivot Buttons - Training & More
View this email in your browser

 

MAY 2015 edition by Wyn Hopkins                                                                 Image source:The Usual Suspects

Hi Dave,

How does the line go?
“The greatest trick that Microsoft ever pulled was convincing the world that Excel was just a spreadsheet”
Well, something along those lines...

If you’re still seeing Excel as just one of “The Usual Spreadsheets”, (see what I did there?), then you are missing out.

It has come a long way since taking over the mantle from Lotus 123. The pace of change, especially in the last 12 months, is astonishing.

For the majority that haven’t been keeping up to speed with all the latest developments in Excel 2010 and 2013, well, watch out because Excel 2016 (Preview) is here already.

This month we'll give you some insight into a few of these new features plus a FREE Pivot Table Add-in that will save you time in your Pivot Table design.

Enjoy and read on...
 







Wyn Hopkins
whopkins@accessanalytic.com.au or +61 411 800 079
Business Intelligence that works
There are way too many topics to cover for Power BI, but this is the one thing that you really need to know about.

Now you can get Power Pivot, Power Query, Power View and Power Map free with Excel 2013 (and even with Excel 2010).

If you’re interested in analysing data or implementing a BI system but haven’t heard of the above before, then get Googling… or give us a call and we’ll give you a demo.
Gain competitive analysis insights from LinkedIn
Power Map, Power Query and Power Pivot combined
Watch a quick demonstration of what’s possible when you combine these elements. 

The data has been extracted from LinkedIn using Power Query and focuses on companies who list Power BI in their specialities. The data is stored in Power Pivot and mapped using Power Map, then it'll simply refresh whenever required by clicking REFRESH ALL(Excel 2013).

Kudos goes to the ever-informative powerpivot(pro) team for detailing how to do this.
So what have we been up to?
Here’s the outline of a solution I provided to a client recently, which involved using Power Query. Data often comes in a matrix layout like this:



But it is much more useful for analysis if the data is re-organised in columns (see below), since this data can easily be converted into a Pivot Table or referenced via SUMIFS and more.



In “the olden days” (i.e. 12 months ago) you’d need some VBA code or a lot of manual messing about to do this, however Power Query has come to the rescue.

The “Unpivot” functionality in Power Query is simple and brilliant. 
  1. Click in the original table of data
  2. Power Query > From Table
  3. Transform > Unpivot Columns
  4. Save and Load Data
And it’s linked, so if you add more data to your first table, just click refresh and the 2nd table updates! This is awesome.  
And in other news...

3 Bonus Pivot Buttons you really need 

If you’re a regular Pivot Table creator then download this free add-in to speed up your Pivot Table development.
 

Too long to handle….

Surprisingly Excel has trouble handling numbers more than 15 digits long. Now that’s a BIG number if you’re talking dollars, but not so big if you have a combination of GL Code, Cost Centre and Company Code.

The problem?
Conditional formatting presents the wrong results and LOOKUP formula stop working.


Wrap a formula’s result in a single cell?  

If you wish to simulate the ‘Alt+Enter’ behaviour in a cell with a formula (i.e. push some part of the formula result to the next cell), all you need to do is to add &CHAR(10)& between the respective parts of the formula. To see the result, you will need to turn on text wrapping for the cell.



Note that I’ve also spaced the formula out to make it more readable, there’s no issue adding spaces in your formula. I want to use this but I’m not sure where it will come in handy. Let us know if you come up with a use….
Upcoming Event
Deloitte Access Economic will be presenting at the next Financial Modelling in Excel Meetup on Tuesday, 16th of June.
RSVP is essntial. Click here for more information.
Training Schedule
Run a course in-house!  It's more cost-effective, more customised, and more flexible!  
Just hit Reply to enquire.

Mastering Financial Modelling

07-10 July 2015 Johannesburg, South Afica

More info >>>

Financial Modelling in Excel Training Course

24-27 Aug 2015 Hong Kong

More info >>>

Financial Modelling in Excel Training Course

7-8 Sep 2015 Perth WA

More info >>>
This training course gives you knowledge of the most useful functions in Excel for financial modelling, a sound understanding of financial modelling and company valuation concepts, and experience in designing solutions to real-world financial modelling challenges.
Share this newsletter...
Like
Share
Tweet
Forward to Friend

About Access Analytic

Access Analytic provides AMAZING Excel solutions that enable companies to grow faster, reduce costs & reduce risks.
Our client portfolio includes Chevron, Woodside Petroleum, Rio Tinto, BHP Billiton, National Bank of Australia, Wesfarmers and Midland Brick, to name a few. 

We run both public and in-house training courses for clients in Australia, Asia-Pacific, Europe, the Middle East and Africa.
 

Let's connect!

Facebook
Facebook
LinkedIn
LinkedIn
Twitter
Twitter
Website
Website
Copyright © 2015 Access Analytic, All rights reserved.

unsubscribe from this list    update subscription preferences