Microsoft Power BI Intermediate

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.

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.

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.

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.

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.

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.