Microsoft Power BI Intermediate
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.
In this lecture we learn about SQL and discover how it can be used to extract data from databases.
In this lecture I introduce SQL Server Management Studio. A tool that helps you to write accurate SQL for querying SQL Server.
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.
Writing SQL queries
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.
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.
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.
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.
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.
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.
Creating the right data model for analysis
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.
In this lecture we build the transaction table with SSMS and execute the query in Power BI.
In this lecture we build out the Product table and add it to our Power BI model.
In this lecture we see a common problem where the Customer information is incomplete. I introduce a new query operator to handle this.
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.
In this lecture we review the model created and create a visualisation based on the model.
Cleaning data to make for a better model
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.
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.
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.
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.
Generating insight with DAX calculations
We start from the basics and learn what DAX is and how it can be used.
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.
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.
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.
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.
Creating executive ready dashboards
In this lecture we'll kick off by reviewing the data model and creating the 1st row of the dashboard.
In this lecture we'll finish off the dashboard by adding a geographical visualisation, heatmap and multi-row card.