4 out of 5
4
182 reviews on Udemy

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
Instructor:
Lee Hawthorn
1,373 students enrolled
English [Auto-generated]
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.

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.

Getting Started

1
Introduction
2
Objectives: SQL basics
3
SQL Basics

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

4
SQL Tools

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

5
Installing SQL Express,SSMS, Sample Database

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.

6
Summary: SQL Basics

Writing SQL queries

1
Objectives: Writing SQL Queries
2
Writing SQL in SSMS
3
Joining two tables with SQL

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.

4
Different types of Joins

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.

5
Joining multiple tables

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.

6
Summarising a table

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.

7
Creating SQL calculations

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.

8
Filtering data with the WHERE clause

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.

9
Summary: Writing SQL queries

Creating the right data model for analysis

1
Objectives: Creating the right model for analysis
2
Turning database tables into star schemas

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.

3
Building the Transaction table

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

4
Building the Product table

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

5
Building the Customer table

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

6
Building the Calendar table

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.

7
Reviewing the model and creating a visualisation

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

8
Summary: Creating the right data model for analysis

Cleaning data to make for a better model

1
Objectives: Cleaning data to make for a better model
2
Fixing the customer issue

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.

3
Fixing issues with Power Query

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.

4
Changing the shape of data with Power Query (no SQL required)

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.

5
Transforming Excel data and adding it to the model

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.

6
Summary: Cleaning data to make for a better model

Generating insight with DAX calculations

1
Objectives: Generating insights with DAX calculations
2
Introduction to DAX

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

3
Creating DAX measures

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.

4
Creating measures with expressions

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.

5
Adding to the filter context programatically

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.

6
Manipulating the filter context

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.

7
Summary: Generating insights with DAX

Creating executive ready dashboards

1
Objectives: Creating executive level dashboards
2
Starting the dashboard

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

3
Completing the dashboard

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

4
Summary: Creating executive level dashboards

New features as of Nov-16

1
Introduction
2
Forecasting
3
Extracting information from fields
4
Arc GIS maps
5
Shape maps (ESRI)
6
Drilling to data
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4
4 out of 5
182 Ratings

Detailed Rating

Stars 5
73
Stars 4
63
Stars 3
36
Stars 2
7
Stars 1
3
29fea585e9bdd96420d04e38b30cc13c
30-Day Money-Back Guarantee

Includes

3 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion