Microsoft Power BI Intermediate

Take your data modelling skills to another level and learn how to use SQL, DAX with Power BI to drive change
4.1 (26 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
413 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 46
  • Length 3.5 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 3/2016 English

Course Description

This course follows on from my earlier course 'Learn Microsoft Power BI'. The skills you learn on this course will give you the tools to manipulate data at different levels in order to create the insight you want. You'll be able to create insight with calculations and develop highly formatted dashboards suitable for publication.

Acquire the Skills to be a Data Ninja

Learn how to:

  • Write SQL to query databases
  • Clean dirty data to get it ready for modelling
  • Create data models that unlock insights from multiple data tables
  • Enrich the model with DAX calculations

Communicate insights by

  • Creating executive level dashboards

Power BI is a new tool that empowers you to be a master of data rather than a servant of it.

Previously these skills were limited to experts but with the modern software the technical barrier has been lowered to such an extent that most people are capable of producing and sharing dashboards.

The prerequisites I have are

  • Students must be data literate.
  • Excel users will have no problem learning Power BI.
  • I'm assuming you've completed my earlier course 'Learn Microsoft Power BI'.

SQL or BI developers probably won't get a lot from this course apart from DAX Calculations and Dashboard skills.

The type of terminology used on this course includes, Tables, Rows, Fields, Joins, Relationships, Models, Calculations, Filtering, Visualizations.

The course is taught using screencasts, slides.

The files I use on the course are available for you to download. As long as you install SQL server with the sample databases you'll be able to follow along.

It can take between 2.5 and 3 hours to take this course.

There are over 2.5 hours of screencasts.

The course is structured using key learning points, allowing you to build upon skills as you progress through the course. At the end of the course you'll have the skills to manipulate data to suit the requirements for complex dashboards.

The skills you'll gain on this course will help boost your career as you'll be seen as a powerful steward of data.

Power BI is becoming increasingly in demand in organisations of all sorts of sizes. Take advantage of this opportunity today and hit that enroll button.

What are the requirements?

  • This course follows on from my earlier course 'Learn Microsoft Power BI'. I recommend students complete that course first as I won't be covering the material that was included in the earlier course.
  • For the SQL part of the course I use SQL Server Express with SQL Server Management Studio. If you want to follow along you can install the freely available SQL Server Express (covered in the course).
  • Power BI Desktop should be installed. This is covered in the earlier course.

What am I going to get from this course?

  • Create SQL SELECT queries to extract data from databases.
  • Learn to use the power of the database to achieve high performance queries.
  • Write DAX calculations to generate powerful insights on data
  • Clean and transform dirty data to get it ready for modelling.
  • Create executive level dashboards.

What is the target audience?

  • This course is targeted at Excel users who are able to format a table and write simple formulas such as SUM(A1:A10).
  • Excel knowledge is not required. Anyone who understands basic tables should be fine with the course material.
  • If you are a BI or SQL developer you may find some value in this course from the calculations but for modelling and SQL you're unlikely to learn much.
  • You will be learning a new formula language that is similar to the one in Excel but in many respects quite different.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Getting Started
Introduction
Preview
01:50
Objectives: SQL basics
01:10
04:21

In this lecture we learn about SQL and discover how it can be used to extract data from databases.

02:19

In this lecture I introduce SQL Server Management Studio. A tool that helps you to write accurate SQL for querying SQL Server.

09:35

In this lecture I take you through installing SQL Express and SSMS along with 2 sample databases I refer to in the lectures. This will allow you to follow along for the SQL lectures and practice your skills.

It's quite possible you already have SQL Server installed by your office IT department. But if your're learning from home you can install SQL Express which for the purpose of this course is the same as the full production version of SQL Server. I must warn this lecture is around 10 minutes but the actual process to install everything is around 30-40 minutes so please check you have enough time before starting. And get yourself a cup of coffee.

Summary: SQL Basics
00:38
Section 2: Writing SQL queries
Objectives: Writing SQL Queries
01:55
Writing SQL in SSMS
03:53
09:52

In this lecture we find the data we need exists in 2 tables. We need to write a query that selects from both tables - Product Name & Product Sub Category Name. I introduce SQL JOINS to handle this common problem.

04:56

In this lecture we look at the different JOIN types that gives us the greatest flexibility when it comes to creating the query we want.

07:36

In this lecture we continue to review SQL JOINS but this time looking at joining 3 tables. We also learn a new skill called Aliasing (renaming) fields and tables as this helps us when writing big queries.

03:35

In this lecture we have a situation where we need to group rows in a table and sum up the results. We can use a feature of SQL called GROUP BY to do this.

09:32

In this lecture we look at creating calculations in SQL that are executed by the database. We start with a simple calculation and end with a conditional calculation that gives you lots of power.

03:29

In this lecture we look at filtering data from the table at the SQL level. This is useful when the table is large and you only need a subset of rows. There's no point bringing all the data into your model if you don't need it.

Summary: Writing SQL queries
00:59
Section 3: Creating the right data model for analysis
Objectives: Creating the right model for analysis
00:42
07:48

In this lecture we go through a small amount of theory of data modelling so that when we move onto the building you have a good understanding of what & why we're doing it. You'll find a zip file in this lecture containing all of the SQL for this section.

04:46

In this lecture we build the transaction table with SSMS and execute the query in Power BI.

03:49

In this lecture we build out the Product table and add it to our Power BI model.

03:03

In this lecture we see a common problem where the Customer information is incomplete. I introduce a new query operator to handle this.

03:50

In this lecture we use SQL code to build a calendar table. The calendar tables lets us slice and analyse the sales data by lots of different calendar attributes such as month, quarter, year.

03:59

In this lecture we review the model created and create a visualisation based on the model.

Summary: Creating the right data model for analysis
00:43
Section 4: Cleaning data to make for a better model
Objectives: Cleaning data to make for a better model
00:48
06:17

We had an issue in an earlier lecture where we had 2 types of customers in the database. Stores and People. We only require 1 Customer field in the model so we have to make a change.

03:35

In this lecture we apply the same fix we did in the last lecture but this time by using Power Query. Doing it with Power Query involves no SQL. I hope you'll see how easier it is. The reason I've shown both ways is to give the skill solve problems in all circumstances.

08:22

In this lecture we continue to explore the features of Power Query in Power BI to transform data. We can do much of this with SQL but many times you're not in a position to write SQL, especially if the data didn't originate from a database.

06:33

In this lecture we've been given data from the Project team about Project Spend. It's come as a horrible Excel worksheet. We'll transform the worksheet with Power Query and add it to the model in way that provides interactions as if the data came from the same source. It's this mashup capability that gives Power BI the power and flexibility.

Summary: Cleaning data to make for a better model
00:53
Section 5: Generating insight with DAX calculations
Objectives: Generating insights with DAX calculations
00:40
09:06

We start from the basics and learn what DAX is and how it can be used.

07:13

In this lecture you'll begin to learn why measures are useful. We start off by creating a simple measure and introduce to important concepts in DAX called the Row Context & Filter Context.

05:28

In this lecture you'll learn how to apply an expression slightly more advanced than SUM() against a table using a measure. This gives you lots of flexibility to create calculations on subsets of rows.

07:14

In this lecture we advance into DAX to explore how we can programmatically add to the filter context. This is very powerful as we can apply fine control over which rows we want to calculate. Sales above certain threshold for instance.

05:18

Rather than add to the filter context we can get complete control over it i.e. ignoring the filter from the visualisation. In this lecture we'll learn about the CALCULATE function - the most powerful function in DAX in my opinion.

Summary: Generating insights with DAX
01:12
Section 6: Creating executive ready dashboards
Objectives: Creating executive level dashboards
00:36
04:55

In this lecture we'll kick off by reviewing the data model and creating the 1st row of the dashboard.

06:07

In this lecture we'll finish off the dashboard by adding a geographical visualisation, heatmap and multi-row card.

Summary: Creating executive level dashboards
01:19
Section 7: New features as of Nov-16
Introduction
01:13
Forecasting
07:22
Extracting information from fields
06:19
Arc GIS maps
05:43
Shape maps (ESRI)
02:51
Drilling to data
01:32

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Lee Hawthorn, Data & Finance professional

I'm a business intelligence professional who has 10 years’ experience turning data into information. I work with companies in the UK & Ireland to help them by building systems that provide reports and dashboards in a fully automated manner.

Qualifying as a Chartered Management Accountant in 2005 and a degree in Performance Management. Before working in the field of BI I worked in Finance as a Financial Analyst.

I have given training to over 200 people on Excel & Microsoft BI. I've received excellent feedback too.

I learnt to code as a child but I'm not a professional coder. This allows me to relate to non-technical people and explain concepts in easy to understand terms.

Having worked for some of the largest organisations I’ve seen personally have BI can be used to improve business performance. I blog about BI & Data Analytics see blog link in my profile.

I have a passion for sharing knowledge as I know it has the power to change the world.

Ready to start learning?
Take This Course