Power BI Boot Camp

I'm pretty excited to announce that we have our very first Power BI Boot Camp coming up in Vancouver, BC on Feb 22-24.  This is something that I've wanted to do for a while, and it's going to be awesome!

image

The boot camp is going to be 3 full days of hands on Power BI, and is intended to get you up and running, building cool solutions that will impact your business.

Why do you care about the Power BI Boot Camp?

Okay, I get it… you're an Excel person, and while you've heard of Power BI, you're not quite sure if you need it or not, and certainly don't know if you can justify the cost of a 3 day Power BI Boot Camp...

I honestly believe that the answer to both questions is an emphatic yes!

Top 5 reasons you need to learn about Power BI:

We'll cover the "how do I convince my boss" near the end of the post, but first, let's talk about what Power BI is, and why it is relevant to you.

  1. A major reason that companies are behind in their Business Intelligence development is that they are using outdated versions of Excel and cannot (or are afraid) to upgrade.   Power BI Desktop is a separate program that sits completely outside Excel, meaning that you can run the most up to date business intelligence tool DESPITE the version of Excel you're stuck on.  I'm looking at you Excel 2003 & Excel 2007 users. Winking smile
  2. You can do a TON of stuff with Power BI for free.  Yes, there are services that cost a nominal fee, but you'll be surprised at how much you can do for absolutely nothing.  The investment you make here will pay back in spades as you begin to automate tasks that were taking you huge amounts of time, and get the ability to deliver true business intelligence to your stakeholders.
  3. Power BI's data collection experience is based on Power Query, and it's modelling experience is based on Power Pivot.  So if you have those skills from your Excel journey, they will be totally relevant in Power BI.  And if you don't?  The new skills you learn at this course related to these areas are portable back to Excel as well.  (So again, if you're stuck on an older version of Excel, this is your chance to upskill BEFORE your Excel is updated.)
  4. If you've ever tried to share Excel dashboards, you know how hard it can be to do so and maintain them.  And if your end users want their reports accessible and interactive on mobile?  Forget it.  But Power BI solves these issues, including delivering interactive reports to your mobile phone (yes, even your non-Windows phones!)  Rest assured, however, that if your company is cloud averse, this tool is not dead to you, and it's still worth coming.
  5. You'll be learning in a small class environment from a world class expert who cares about delivering value.  I've been there.  I know how hard your job is.  And I want to help you become more effective.  That is what my company is all about, and I'm very serious about it.

What will the Power BI Boot camp cover?

To be completely honest, the better question is probably "what won't we look at?"  I've got all kinds of stuff cooking for this.  When I first started drafting the Power BI Boot Camp outline I was wondering how I'd ever fill 3 full days.  Now I'm trying to figure out what to cut so that I can fit it all in.  Keep in mind that it's not going to necessarily be in the order below, as some topics are MUCH bigger than the summary I'm providing, but here's a bit of an overview…

Where it all starts…

We'll start by building a basic Power BI solution built on an Excel file.  Some minor data modifications, some cool visuals and then publishing it to the cloud service, we'll walk through the most basic of Power BI journeys.  I want to do this so that you can see just how easy it is to build a cool solution that works and interacts, like this one.  (Go ahead... click any of the bars and see how it cross filters in your web browser!)

More data, more transformations, and some modelling…

From there we'll start to dive deeper, teaching the techniques that allow you to build more robust models and solutions.  We'll spend a bunch of time looking at different data sources, as well as some of the more complex methods for fixing bad data to make sure that you can use it in your solutions.  We'll also show you how to link these tables together so that they filter nicely across multiple visuals.

image

Let's get visual…

Power BI is all about getting visuals with your data.  It has a big collection of visuals, as well as an ever growing gallery of custom visuals as well.

While I won't promise that we'll look at each different visual in the product (we only have 3 days), we are going to look at a lot of them, including some of my favourite custom visuals.  I want to show you how to create them, format them, and what data you need to get the best of them to work.

image

We're building dashboards to tell a story here, so I want to try and get that as close to real life as possible for you.  From bar, column and line charts, to maps and bullet charts, I really want to get you comfortable with these, as well as how to control the interactions between them.

And speaking of Dashboards… do you know the difference between a report and a dashboard in Power BI?  That will get clearly explained here as well.

Getting the numbers right…

We'll get into working with the DAX formula language, and understand how to add and override filter context to make your formulas show exactly what you want when you filter any visual.

How about Calendar intelligence?  I have a huge passion in this area too.  The Power BI Boot Camp won't just teach you how to build your own calendar table, you'll also leave armed with a collection of DAX calendar intelligence measures.  Those measure patterns are super important as they'll allow you to report results correctly when you ask for Month to Date data or Month to Date for 2 months ago.

image

Sharing is caring!

We'll look at sharing via the web first, both for internal and external users on an invite-only basis.  But even more, I'm also going to walk you through embedding your reports in a web page so that you can show the world how awesome your company is.

In addition, you'll want to bring your phone with the Power BI app installed.  Why?  Because I'm going to make sure you get set up with your reports and dashboards delivered to your phone in a mobile optimized fashion where you can even annotate your reports by hand.

image

And if you'd prefer not to be cloud based?  We'll look at creating Power BI templates that you can distribute within your organization as well.

You know… while we're talking about sharing… have you ever sent a report to a manager only to have them come back with a one of those "can you show me this?" type questions?  Let's try and solve that little problem by allowing our manager to ask their own questions using natural language queries.  In other words, how cool would it be if your manager could just type "Bar chart total stays by city" and it did it, even if you've never built that report?  Because they can.

image

And what about updates and security?

Wait… how do I keep this stuff up to date?  And how do I ensure that only the right people are looking at their data?  Well of course we'll cover those topics!

Not only will we look at scheduling refresh for cloud hosted data sources, but also your on-premises data sets via gateways and 3rd party applications.  Ooohh… connecting data to the cloud… can I hear anyone saying "Security!"  Well yes, so let's talk about both encryption and also how to restrict the data sets to show only relevant rows to the correct people.  (And let's face it, you will want that last part even if you keep your data local!)

So why Ken's Power BI Boot Camp then?

My goal is to deliver real value to you.  Anyone who has been to any of my courses in the past knows this:

  • It's virtually impossible to find someone who has more passion for what they teach than me
  • We limit the class size so that it doesn't get too big.  A huge part of the reason for this is because…
  • My style is to roam the room and help people keep up with the content so that everyone learns
  • I provide completed examples for all the work we go through, with catch up points along the way, just in case the above isn't achievable
  • I provide a ton of supplemental resources as well

I've been to classes before, and I know that it can be somewhat difficult to apply the course materials to your own work.  For that reason the Power BI Boot Camp is designed like all my courses: We will build solutions like you'd expect to do so in the office, exposing the pitfalls where the software doesn't work as you'd logically expect.  This is intentional, as we teach you not only how to do things correctly, but also how to debug problems as they happen.

What skill set do you need?

Do you need experience working with Power BI in order to come to the Power BI Boot Camp?  Heck no, that's what this course is about!  And while some Excel knowledge is helpful, you're certainly not going to see a test on Excel functions here.

Ultimately what you need is a hunger to learn; a desire to change your reporting world.  If you can bring me that enthusiasm, I'll teach you what you need to know in order to make that happen.

Making the Case to Your Boss

I managed an accounting department in my former life, so I totally get this.  We're all about data here, so here's the up front details:

The cost of the course is $1,495 plus GST.  It includes your breakfast and lunch all 3 days, but you need to look after your dinner and hotel room (if necessary).  So now you can work out the total cost.

I'll let you do the math and work out how many hours per week you need to save yourself in order to break even.  If you spend a significant amount of time cleaning up data to be used in Excel, you'll find that you'll break even on this alone.

Honestly though, the true value proposition of this course isn't the ability to break even on a labour basis, it's about the opportunity costs.  Opportunities like:

  • Identifying programs that are costing your company money which don't earn returns.
  • Finding the best paying leads to pursue for greater sales.
  • Delivering value to your stakeholders when and where they need it.
  • Reducing inventory during slow seasons to reduce carrying costs.
  • Highlighting key metrics that are under performing.
  • Building solutions to blow the minds of your clients or stakeholders.

Every business is different, but ultimately it's the opportunity to get better information into the hands of those who make the decisions, be it you or your boss.  That benefit FAR outweighs the price we charge, and will break even for you VERY quickly.

I hope to see you at the course.  It's going to be awesome, and it will change your data life forever. 🙂

Register for the Boot Camp here.

October News and Events

It’s a busy month here at Excelguru. Instead of a technical post we wanted to catch everyone up on our October news and events!

Live Course: Master Your Excel Data October News and Events

Ken is teaching a LIVE, hands on course in Victoria, BC on Friday, October 21 from 9:00am-4:30pm. This session is great for anyone who has to import and clean up data in Excel and will change the way you work with data forever! Ken will teach you how to use Excel Tables, Pivot Tables and Power Query. Space is limited to only 20 attendees, so don't miss out on your chance to sign up. For full details and to register for the session, visit: http://www.excelguru.ca/content.php?291-Live-Course-Master-Your-Excel-Data.

October News and Events: Power BI Meet-up

The next Vancouver Power BI User Group meet-up is happening on Thursday, October 13 from 5:30-7:00pm. Scott Stauffer, Microsoft Data Platform MVP, will be presenting on How to Operationalize Power BI. Together we’ll look at some solutions that might help pass your Power BI solution over to IT to manage enterprise-wide. Dinner and soft drinks will be provided. View the full details and sign up to attend at: http://www.meetup.com/Vancouver-Power-BI-User-Group/events/234126999/.

Microsoft MVP Award Received

For the 11th straight year, Ken has received the 2016 Most Valuable Professional Award from Microsoft! The previous 10 years, Ken’s award has been in the Excel category, but this year’s award is in the Data Platform category. The new category reflects the work he’s been doing this past year with Power Query and Power BI. Congratulations Ken, your guru status remains assured.mvp_horizontal_fullcolor

Our Team Has Grown

As we mentioned the other day, Rebekah Sax has recently joined the Excelguru team. She brings with her a wealth of experience in marketing, communications, event planning and administration. Please join us in welcoming Rebekah as she helps us make new connections and continue to grow.

Last Chance to register for Excel Summit South

Excel Summit South 2016

I’m only a few days away from my flight to New Zealand to kick off the first leg of Excel Summit South.  I’m really looking forward to it.  And if you’ve been sitting on the fence as to why you should attend… just ask Jeff Weir.  (Seriously, read his post, it’s awesome!)  But you need to act quick here, as it’s pretty much your last chance to register for Excel Summit South now.

What it’s all about

This will be a great opportunity to keep up with modeling practices, extend your analysis skills, and see what’s happening with Excel.  Full details about the Summit can be found at the Excel Summit South 2016 web page, but you can read about some of the high points below, or – did I mention that you should read Jeff Weir’s Why I’m going to Excel Summit South. (And why you should too) post on Daily Dose of Excel?

When and where is Excel Summit South?

The Summit will take place at these cities on the dates shown:

  • Auckland: Thurs-Fri 3-4 March (register by 28 February)
  • Sydney: Mon-Tues 7-8 March (register by 1 March)
  • Melbourne: Thurs-Fri 10-11 March (register by 6 March)

Last Chance to register for Excel Summit South - Discounts available!

As an additional incentive, we’ve arranged a last chance registration discount, but only up to the date above.  Simply REGISTER HERE and use the code LASTCHANCE to save 30% on your registration fees.

23 Excel Master Classes

With your registration, you can choose from 23 master class sessions over two days.  There are twin tracks for modelers and analysts alike, and you can jump between if you’d prefer to do so.

Modeling Track – Manage Spreadsheet Chaos, Testing Spreadsheets, Avoiding Common Errors, Modeling Best Practices, Simulation Analysis Without VBA, Power Pivot.

Analysis Track – Tables, Pivot Tables, Power Query, Data Visualization, Dashboards, Automating Excel.

The Who’s Who of Excel…

Learn from six (seven!) leading Excel MVPs as they discuss the Excel topics most useful to you.

Liam Bastick (AU), Zack Barresse (US), Bill “Mr Excel” Jelen (US), Ken Puls (CA), Jon Peltier (US), Charles Williams (UK), with a guest appearance by Ingeborg Hawighorst (NZ) in Auckland.

Hear industry leading speakers about Financial Modeling best practices, standards and spreadsheet risk.

Smita Baliga (PwC), Félienne Hermans (Delft U), Ian Bennett (PwC), Andrew Berkley (F1F9).

Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.

Ben Rampson and Carlos Otero from the Microsoft Excel product team.

Network and Interact

As if the classes weren’t enough, we’ll also have Panel Discussions, Ask The Experts sessions, Demonstrations of Commercial Excel Tools, and even an Evening Meet-up where you can ask your Excel questions over a beer.  (Full caveat… the quality of the answers may decline as the evening progresses!)

A shout out to our principal sponsor

PwCOur principal sponsor for this Summit is PwC Australia and PwC New Zealand.  We appreciate them coming on board to host this event!

Excel Summit South

Yes, you read that right.  If you haven't heard yet, I'll be coming to New Zealand and Australia in just under a month!  And the entire purpose of the trip is to come and share Excel knowledge for with my friends and colleagues south of the Equator.

I'm pretty jazzed about this, and not just because I get to go to the southern hemisphere for the first time in my life.  And also not just because I get to talk about Excel when I'm there.  That would be enough, but no… I'm jazzed because I get to do this with some pretty cool friends who are world respected leaders in their area.

Excel Summit South

The main purpose for my trip is the Excel Summit South conference.  Two days, two tracks of advanced Excel material in 3 different cities:

  • Mar 3&4: Auckland, New Zealand
  • Mar 6&7: Syndey, Australia
  • Mar 9&10: Melbourne, Australia

And the best part about this conference is that – while it's sponsorsed by Price Waterhouse Coopers – regisration is open to everyone.  So basically, you can check the schedule, pick the sessions that interest you, and learn things that will impact your Excel skills.  In other words, if Valuation Modelling isn't your thing, then you can go to a Power Query class.  And if Power Query isn't your thing… well… you're kind of odd, but there will be something that is.  Smile

The cast and crew for this conference really can't be beat.  Charles Williams, Bill Jelen, Jon Peltier, Zack Barresse, Liam Bastick are all Excel MVP's on the bill (as well as Ingeborg Hawighorst in our New Zealand apperance.)  Heck, we've even got a couple of guys from Microsoft attending and presenting as well.  This is a fantastic opportunity to not only meet some of the big hitter independant Excel folks out there, but also to talk to Microsoft directly.  How can you pass that up?

My Sessions

If it hasn't shown yet, I'm seriously looking forward to this conference.  Personally I'll be leading two sessions:

An End to Manual Effort: The Power Query Effect

What Power Query is, why you care, and how it can re-shape and transform the data experience.  What's really special about this session is that I'm going to take this data and turn it over to Jon Peltier who is then going to take it and turn it into a dashboard.  This is perfect, as I'm demoing how to automate data cleanup, and Jon will show you how to use it to add true business value… the real life cycle of Excel data in just a couple of hours.

The Impact of Power Pivot

This one will be fascinating, especially for those who have never seen Power PIvot in action before.  In just an hour I'll show you how big business BI (business intelligence) is at the fingertips of anyone with an Excel Pro Plus license.  It's applicable to companies as small as one employee, and scales up to multi employee small businesses, and even large businesses.  (Departments in large corporations eat this up, as they effectively just act as a small business within the larger whole.)

Register for Excel Summit South now!

Tickets are going fast for this event in all cities, so we ecourage you to register sooner rather than later, and hope to see you there!  You can find out more details and register at:  https://excelsummitsouth.wordpress.com/

Last PowerQuery.Training Class of 2015!

There will be a regular blog post coming later this week, but we wanted to just throw out a quick heads up that we are currently accepting registrations for the last PowerQuery.Training class of 2015.

Registrations are open now for the class which begins on November 24, 2015.  This will be your last chance of 2015 to get an in depth training class on the best damn tool to hit Excel in 20 years.  (Sorry Power Pivot, but Power Query is going to reach more people overall.)

For more details on why you need to take this amazing live online workshop, check out the details here.

To register, you can follow this link (and click the Register button on the bottom right of the page.)

And don’t forget that when you register you get a free digital copy of our amazing new M is for Data Monkey book too.

Hope to see you there!

What Power Query Functions Exist?

I know that this topic has been covered before by others, but I think it’s still pretty valuable for a user to be able to figure out what Power Query functions exist, especially since they are often different than what we’re used to in Excel.

NOTE:  This article was updated 2015-05-20 at the request of a reader to include more coverage on implementing the discovered function into the solution.

 

Power Query Functions Documentation on the web

There’s a pretty good resource site available on the Microsoft Support site.  Personally I have that one bookmarked and head over there often when I’m looking for a new function to do something.  I find that with a quick CTRL + F on the page, I can quickly search and narrow in on the function I think I need in order to learn it’s syntax.

To be fair, I’m not always in love with the actual examples (many lack a power query UI view), but overall the site is fairly useful.

Power Query Functions Documentation in the client

Now that’s all good, but what if you’re working on a plane with no WIFI, and you need to figure out the syntax for a new function?

As luck has it, there is a way to pull up the list for most functions right in the client.  To do this, I:

  • Clicked Add Column –> Add Custom Column
  • Typed a 1 and clicked OK
  • Went to the Power Query formula bar and typed the formula below.  (Notice that this is case sensitive)

 =#shared

(Why the custom column? Because typing in the formula bar replaces the previous step, and I want to be able to revert to that since it’s part of my logic:

image

Now, you’ll see you get a list of (almost) all the functions that you can access:

SNAGHTML894ffc53

Now, let’s assume I’m trying to find a formula to remove certain characters from a text string.  I really need to search for “Text.”, but there isn’t a search option.  No big deal, let’s convert this list into a table:

SNAGHTML89522a10

Once we’ve done that, we get a nice table of all of the functions, and we can filter them to our heart’s content.  Here’s my table filtered down to just rows that begin with “Text”:

SNAGHTML897b95c3

And a page or so down, I found something that looks like it might work:  Text.Remove.

Investigating the Function Syntax

I clicked on the green Function beside the Text.Remove entry.  It pops up an Invoke Function box, and behind that is the syntax for how it’s supposed to work.  So that’s pretty cool.  I tried it out with some text, as shown below:

txtremove1

Clicking OK returned the following:

txtremove2

Now this is a bit… weird… and frustrating. Value? Why Value? (I actually don’t know why, you’d think it would have been the function name, wouldn’t you?)

I stepped back to the Value step of the query, as I wanted to look at the syntax page that popped up behind the Invoke Function dialog:

image_thumb.png

 

My only complaint here is that once you land in this window, the only indicator of the actual function name is in the smallest font on the page, buried in the middle. You’d think that the name would should up a little more prominently. Regardless, I copied the name of the function, then stepped back to the Invoked FunctionValue step and replace Value in the formula bar with the function name:

txtremove3

Perfect, it works.

Implementing the Function in the Solution

Now let’s see if I can get it into my original query. To do that I:

  • Copied that entire line of M from the formula bar,
  • Selected the Source step (I wouldn’t be able to do this if I had typed #shared while I had the Source step selected originally),
  • Choose to Add New Column –> Add Custom Column –> Accept the inserted step,
  • Pasted the copied M into the formula area, and
  • Replaced the original text (“My –Dog –Has –Fleas”) with the name of the appropriate column from my data set.

Visually, it looks like this:

txtremove4

 

And then I checked the query to see that it worked:

txtremove5

 

Cleanup

Now that I’ve been able to explore the functions and found and implemented the one I’d like to use, I can just knock off the extra steps shown below in yellow, returning me back to my next step:

txtremove6

 

 

 

Learning more about Power Query functions

For reference, this is one of the many things that Miguel and I will be covering in our upcoming Power Query training workshops.  Learn more about the workshop and register here:  http://powerquery.training/course/

PowerXL Course Live in Victoria, BC

We are very excited to announce that we will be hosting an "Introduction to Power Excel" session in Victoria, BC on June 6, 2014.

PowerPivot is revolutionizing the way that we look at data inside Microsoft Excel. Allowing us to link multiple tables together without a single VLOOKUP statement, it enables us to pull data together from different tables and databases where we never could before. But linking data from multiple sources, while powerful, only scratches the surface of the impact that it is making in the business intelligence landscape. Not only do we look at PowerPivot in this session, but we'll also explore the incredible companion product Power Query; a tool that will surely blow your mind. Come join Ken as he walks you through the process of building a Business Intelligence system out of text files, databases and so much more.

Full details, including an early bird signup offer, can be found at http://www.excelguru.ca/forums/calendar.php?do=getinfo&e=42&day=2014-6-6