Course Description
We know that data is not usually stored in nicely curated databases and often – even when it is – the data analyst doesn’t have access to it. Instead, we must piece together data provided in text files, Excel files, web pages, and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops with this workshop!
The Self Service BI Academy begins with a deep exploration of Power Query. Built-in to both Excel and Power BI, Power Query can clean, reshape, and combine your data with ease – no matter where it comes from. Converting ASCII files into tables, combining multiple text files in one shot, and even un-pivoting data is not only simple, but an investment in the future. With Power Query’s robust feature set at our fingertips, and our data clean and ready to be used, we’re now ready to explore creating dynamic business intelligence models that are refreshable with a single click.
Next, we introduce the benefits, concepts and key terminology of Dimensional Modeling. Based on the Power Pivot Data model, it’s this portion that lays the cornerstone of your reporting solution. You’ll learn the difference between Facts, Dimensions and Relationships, where they live, and how to design and link their parent tables correctly. You’ll also learn some key Power Query recipe patterns for solving two of the most frequent challenges when trying to relate tables together.
While learning to create the proper dimensional model is critical to every solution, the visible magic happens when we talk about DAX in the next section of the workshop. This powerful formula language allows us to report on much more than just the ‘Sum of Sales’. In this workshop, you’ll learn how to create a variety of DAX measures, understand how DAX measures are calculated, and how to control their Filter Context. These are all critical skills for building your own advanced measures for your work.
No course on self-service BI would be complete without discussing calendar intelligence, which is exactly why we cover it. From building calendar tables on the fly to exploring the “Golden Date” pattern, we’ll work through the steps required for extending our model to report based on our own year-end.
Finally, we’ll dive into specific features of Excel and Power BI that every analyst should know. How easy is it to create a Power BI report and use a variety of different visuals for displaying data? How can you publish a Power BI report and share it with other users? How do you manage those permissions? All these questions will be answered!
Throughout this workshop, you’ll be working with both Excel and Power BI. Why? Because the tools and concepts you’ll be learning work in both places. So how do you know which one is right for the job? We’ll talk not only about that, but also how they can be used together in one solution. Come and join us to revolutionize your reporting process!
You can view sample lesson videos, as well as a full listing of the topics covered in this course, further down this page.
Who this course is for?
- Anyone who needs to build reports based on data.
- Excel users who want to ‘upskill’ their knowledge to work with modern features
- Users who want to be able to work with BOTH Excel and Power BI (no matter which program you start from.)
- Attendees who are looking for coaching as they learn a new topic.
What version of Excel do you need?
The versions of Excel we support in the course are:
- Office 365 (preferred)
- Excel 2021
- Excel 2019
- Excel 2016 Professional Plus (note that Home, Business and Premium products do not include Power Pivot)
What skills should you bring?
Experience creating and modifying Excel PivotTables is highly encouraged.
- If you do not have experience in this area, we encourage you to check out our Excel Fundamentals Academy, which will get you prepared to take this program.
If you are comfortable creating PivotTables from a data source, you’ll will be able to get a lot out of this course. But if you have ever run into one (or both) of the following two issues, you’ll know you are absolutely ready:
- You’ve attempted to build a Calculated Field and found the interface and options to be lacking.
- You’ve run into an issue where two data sources cannot be merged together to serve the data for a PivotTable
Ultimately, experience with Power Pivot and Power BI are optional as we will teach you what you need to know in both these areas.
Having said this, if you are already using Power Pivot, here are some key factors that also indicate you are ready for this course. You may have:
- Triggered a “Relationship Between Tables may be needed” error on an Excel PivotTable
- Been told you cannot create a relationship between tables because each column contains multiple unique values
- Received an error upon refreshing your Data Model because a column contains multiple values
- Created a relationship backwards in your Data Model
- Discovered a value that doesn’t seem to filter properly when drilling in to a PivotTable
If any of these sound familiar (or not), we’ll show you how to solve them.
For Power BI users, we recommend that you have experience getting data from a raw data source and building reports. As long as you’re able to do this with some success you’re good to go.
Keep in mind that our journey will start in Excel, but we will teach you the ‘how’ and ‘why’ so that you can apply the techniques in the correct tool for the correct job.
Not sure if this course is right for you?
Visit our Learning Journey page to compare courses by skill level and area of specialization.
Course Structure
This course is a hybrid format of pre-recorded video training interspersed with live online Q&A and coaching with Ken Puls, for direct help with your questions. The Self Service BI Academy is broken down into two main sections:
Section 1: Course Curriculum
The main course curriculum includes over 28 hours of pre-recorded video training, of which approximately 2.5-3.5 hours will be assigned per week. While attendees are able to consume as much as they want at any time, our recommendation is to dedicate one afternoon per week. Doing so allows completion of the entire course over a period of approximately 14 weeks (including a ‘reading week’ every 4th week.)
Section 2: Coached Support
In addition to the main course curriculum, this course also includes access to twice-monthly “Ask Ken Anything” (AKA) sessions for as long as your subscription is active. Each of these sessions is up to 2 hours in length, conducted live and recorded for later review. This is a great way to receive continued coaching on a longer-term basis, as you apply your skills in the real world and run into real world problems.
Questions for the sessions may be submitted in advance, and all of the live AKA sessions will be recorded and shared with participants. Thus, you will still be able to learn from the discussions even if you are not able to join Ken live (due to time zone conflicts, other commitments, etc.) or get called away for part of the session. This also allows you to watch the recordings on your own schedule, and to pause, rewind, and practice more with example files.
If you are not able to join us live for the AKA calls, you can still submit your questions to Ken beforehand and he will answer them in the session. You will then be able to watch the answer later on in the session recording.
As with all of our training courses, we also provide access to our curated Skillwave Support Forum as well, allowing you to get answers on a quicker basis should you need more immediate help.
Total Training Hours
The core of this course contains approximately 51.5 hours of training, based on:
- 28.5 hours of video training
- approximately 11.5 hour of practice time with the example files
- approximately 11.5 hours of AKA sessions (based on 23 calls/year and students attending/watching 25% of the AKA calls/contents)
If registrants attend (or watch the recordings of) all 46 hours of the AKA sessions, this course includes more than 86 hours of training in total.
Get Started the right way with a live Kick-off call
Registration is ongoing with new intakes beginning each month. Upon joining the course, attendees will be invited to join a “Kick-off” call where Ken will walk through the details on how the program works. These calls are generally hosted on the 2nd Wednesday of each month from 9:00 AM – 9:30 AM Pacific Time and 4:00 PM – 4:30 PM Pacific Time. Attendees are encouraged to join the call that makes the most sense for their time zone.
Ongoing Support via ‘Ask Ken Anything’ Sessions
With the exception of December, Ken hosts 2 “Ask Ken Anything” sessions per month – generally on the 1st and 3rd Wednesdays of each month, resulting in approximately 23 AKA calls held each year. Call times alternate between morning and afternoon (Pacific Time), in order to allow students from different time zones the opportunity to join the sessions in person. All AKA calls are recorded, and students are able to submit questions in advance.
Also, you may join any of the AKA sessions for as long as you are subscribed to the course, even those after your initial enrollment semester ends.
Bonus Gifts with this Course
Reviews
Self Service BI Academy
Average Rating
4.9 / 5
Course Update Log
2021-03-22
First release of the course.
2021-08-02
Release of Master Your Data with Excel and Power BI book (formerly titled M is for (Data) Monkey). Course registrants now receive the new second edition instead of the original first edition.
2021-09-28
Updates made to the Power Query Recipes:
- Added an entire new section to the recipe collection that corresponds with the new Academy module on Allocations. Section 65 – Allocations Over Periods contains the following new recipes:
- Allocating Values Over Periods Between Two Dates (65.100)
- Allocating Values Over x Periods From a Start Date (65.101)
- Allocate Evenly to Each Day in Period (65.110)
- Allocate to Month by Days in Period (65.120)
- Allocate Evenly to each Month in Period (65.130)
- Allocate to Year by Days in Period (65.220)
- Allocate Evenly to each Year in Period (65.230)
- Allocate Over x Months from Start Date (65.310)
- Allocate Over x Years from Start Date (65.320)
- Allocate Over x Consecutive Days from Start (65.410)
- Allocate Over x Instances at y Intervals (65.420)
- Added two new recipes to Section 60 – Date & Time Techniques:
- Creating a Custom GetMonthEnds Function (60.850)
- Creating a Custom GetYearEnds Function (60.855)
- Updated a recipe in Section 70 – Miscellaneous:
- Add a Random Number to all Table Rows (70.100)
2021-12-20
Power Query Recipes Updates:
- Added a new recipe to section 20 – Reshaping Data:
- Pivoting Vertical Sets (Recipe 20.130)
- Updated existing recipes:
- Pivoting Stacked Data (Recipe 20.120) – name of the “Integer-Division” column updated to reflect new wording (was formerly “Integer-Divide”)
- Allocate over x Years from Start Date (Recipe 65.320) – the formula logic for the first period was corrected
- Random Numbers (Recipe 70.100) – note added that the recipe is now much easier with versions of Power Query released after October 2021
2022-03-31
Power Query Recipes Updates:
- Added a new recipe to section 00 – Best Practice Architectures:
- Quickly Setting Data Type of All Columns (Recipe 0.550.2203)
- Added a new recipe to section 70 – Miscellaneous:
- Choosing Columns vs Removing Columns (Recipe 70.130.2203)
- Updated existing recipe in section 30 – Combining Data:
- Cartesian Products (Recipe 30.120.2203)
2022-06-27
Updates made to the Power Query Recipes:
- Added a new recipe to section 20 – Reshaping Data:
- Shift Non-Aligned Columns Left (Recipe 20.160.2206)
2022-09-28
Power Query Recipes Updates:
- Added a new recipe to section 20 – Reshaping Data:
- Unfill in Power Query (Recipe 20.170.2209)
2023-03-31
Power Query Recipes Updates:
- Added two new cards:
- Recipe 35.200 – Append all Tables in the Current Workbook
- Recipe 60.950 – Convert a Julian Date to a Gregorian Date
- Updated an existing card:
- Recipe 60.530 (Fiscal Period Columns for 13 x 4-Week Calendars) – corrected the Fiscal Quarter formula
2023-05-01
The original format of weekly 30-minute Q&A calls + monthly ‘Ask Ken Anything’ (AKA) calls replaced with twice-monthly AKA calls that are 2-hours each. This is to better accommodate new semesters kicking off each month instead of each quarter.
2023-07-21
7.5 hours of new and updated content added to the course, including:
- Added a new Architecting Business Intelligence Solutions module, containing 8 new lessons
- Added 4 additional lessons to the end of the Dimensional Modeling Recipes module:
- Adjustments for 12-Month Non-Standard Year Ends
- Fiscal Periods for 12-Month Non-Standard Year Ends
- Adjustments for 364-Day Calendars
- Fiscal Periods for 364-Day Calendars
- Removed the original Dashboarding with Power BI Module, and replaced it with 5 new modules:
- Introduction to Power BI (containing 7 new lessons)
- Power BI Desktop (containing 7 new lessons)
- Power BI Service (containing 8 new lessons)
- Sharing Power BI (The Right Way) (containing 6 new lessons)
- Multi-User Environments (containing 6 new lessons)
- Updated the Excel & Power BI – Better Together module to be called Power BI & Office – Better Together, and
- Replaced 4 lessons with brand new lessons
- Updated 2 of the existing lessons to feature the new Power BI UI
2023-10-06
Power Query Recipe Updates:
- The caution note on Recipe 10.100 – Split Records into Columns has been adjusted.
- The Power Query Academy lesson link on Recipe 10.105 – Split Records into Rows has been updated to go to the revised Splitting Columns lesson.
- The steps in Recipe 20.150 – Remove Top x (Dynamic) Rows have been completely revised to reflect a better technique for this date transformation.
2023-12-22
Power Query Recipes Update:
- Complete rewrite of Recipe 20.150 – Remove Top x (Dynamic) Rows to make it easier to build and avoid pitfalls related to defining your ‘dynamic value’ correctly.
2024-02-13
Course rebranded as Self Service BI Academy to better reflect the expanded scope since it was first released. (The original course name was Self Service BI Boot Camp.) No updates were made to the course content.
2024-04-22
Ten new lessons added:
- Importing Power BI to Excel lesson added to the end of the existing Power BI and Office – Better Together module
- Model Auditing with Monkey Tools lesson added to the middle of the existing Performance module
- New module added after the existing Performance module called From Folder Performance, containing 8 lessons:
- Ideal From Folder Setup
- Why is my From Folder Solution Slow?
- Choosing the Correct Connector
- Offloading Transforms via Snapshots
- Tuning From Folder Solutions for SharePoint
- Comparison of From Folder Method Performance
- Creating Efficient From Folder Solutions
- Do You Really Need From Folder?
2024-06-28
Course Updates:
- New Semi Join lesson added to the Merging Data module
Power Query Recipes Updates:
- New recipes added to Section 30 – Combining Data:
- Left Semi Join – Recipe 30.117
- Right Semi Join – Recipe 30.118
- Recipe 30.100 updated to 9 Ways to Join Your Data
2024-09-05
We have added 5 quizzes to solidify knowledge on:
- Data Cleansing
- Dimensional Modeling
- DAX Measures
- Power BI
- Performance
We have also created 3 new practice labs to provide hands-on experience building a business intelligence solution, which focuses on:
- Data Cleansing
- Dimensional Modeling
- DAX Measures
Finally, we have adjust the recommended schedule to keep to a maximum of four hours per week. We have also split up and renamed the modules in the Performance section (now Week 15) to group them under more logical names.
2024-10-03
New module on Understanding Blanks has been added to Week 10. It includes two lessons:
- Understanding Blanks
- Safely Reducing Dimensions