Learn how to work with data that comes from different sources and is structured in different ways by using Power BI transformations to combine, reshape, cleanse, and enhance that data to create a model that supports reporting and analytics.
Data in its raw form is rarely useful for reporting and analysis. In this course, Modeling Data in Power BI, you'll learn how to use Power BI to transform raw data into meaningful insights. First, you'll learn how to gather data and apply transformations to easily blend multiple sources together, reduce the data to its most useful elements, and prepare the data for exploration. Next, you'll discover how to configure the model to support Power BI features for insightful visualizations, geospatial analysis, and natural language queries. Finally, you'll explore the basics of DAX, Power BI's expression language, to add calculations to your model. When you're finished with this course, you'll have the skills and knowledge of Power BI data modeling that you can use to build your own models that support a variety of analytical requirements and take full advantage of Power BI's capabilities.
Stacia Misner Varga is a consultant, instructor, author, mentor, BI Partner for SQLSkills, and principal of Data Inspirations specializing in Microsoft business intelligence technologies for over 10 years. She is a frequent speaker at the PASS Summit, IT/Dev Connections
conferences, and various SQL Saturday and Microsoft-related events
Course Overview Hello. I'm Stacia Varga, and welcome to the Modeling Data in Power BI course. I work with data all the time as a consultant, helping my clients use and understand their data and as an instructor and author, teaching people how to build data solutions and work with data effectively. The whole point of using Power BI is to enable the exploration and visualization of data whether you're doing this for yourself or for others. Because data in its raw form is not often suitable for this purpose, you need to create a data model that refines and enriches this data to make it more useful and understandable. There are a lot of steps involved in data modeling for Power BI, and Power BI is certainly packed with lots of features for data modeling. In this course, I'll show you how to start with a set of files and incrementally refine and enhance that data to produce a better data model. We'll start by loading data into a model and then shaping and cleansing the data, and we'll learn how the structure of the data model affects how the data displays and see how incremental changes improve the model structure, not only for reporting, but also to take advantage of special Power BI features for geospatial analysis and natural language queries. We'll also learn how to enhance the model with various types of calculations that transform raw data into information. By the end of this course, you'll know how to apply a variety of techniques in Power BI to model your own data. From here, you can continue learning more about Power BI by viewing other Pluralsight courses on Power Query, DAX, and several other courses covering different aspects of using Power BI. Although you don't need to know any programming or data query languages before you start this course, some familiarity with basic data analysis is helpful. Let's get started on this Power BI journey with the Modeling Data in Power BI course here, at Pluralsight.
Using the Query Editor to Prepare a Power BI Data Model Hi. I'm Stacia Varga, and welcome to the Modeling Data in Power BI course. Whenever you need to build a new data model in Power BI, the very first step is to get your data and then figure out how to fix any problems that might exist in that data. That's our focus in this module, and I'm going to show you how to use the Query Editor to get started on a new model in Power BI. The Power BI Query Editor is used for several important tasks in data modeling, two of which I'll describe throughout this module. First, it's the tool you use to load data into Power BI. In most cases, you're literally making a copy of the data and putting it into a specific structure in memory that Power BI uses to display data in a chart or a table or some other visualization in your report. In other cases, such as when you use the DirectQuery or Connect Live options for a data connection, you're telling Power BI where to find the data and how to query it when you want to refresh your report. Throughout this course, I'll be using a specific set of data for demonstrations. So I'll first provide you with a big picture overview of the different types of data that we're going to use and then show you how to load all that data into Power BI. Whenever you load data into Power BI by using the option to copy the data, you usually need to do some data wrangling. In other words, you need to shape the data in some way. This task could be as simple as defining a filter to load only a subset of data into a model, or it can require a variety of transformations to change the data into a format that's better suited for reporting. In this module, we'll look at the more common types of transformations that you can use in the Query Editor to shape your data.
Enhancing a Power BI Model with Relationships and Hierarchies Hi, I'm Stacia Varga. Welcome to another module in the Modeling Data for Power BI course. This time, we focus on enhancing a Power BI model with relationships and hierarchies. Both of these elements are important for successful exploration of the data, but in different ways. Once we have our data from different sources loaded, shaped, and cleansed one query at a time, we need to connect the separate queries together and refine the data even further so that it's ready for creating visualizations. Specifically, the connection between queries is known as a relationship. And I'll start by showing you what happens when your model has no relationships at all and how the addition of relationships is the key to successful data models that contain multiple queries. Under certain conditions, relationships might be detected and added automatically by Power BI. But sometimes they're not added correctly and often not at all. Therefore, you need to know how to review existing relationships and how to add new ones if necessary. Now relationships can be tricky, and I'm only qualified to talk about data relationships here folks. You need to be able to recognize an issue caused by relationships and know how to fix it. Whereas relationships are almost always required in a data model in order to display data correctly, another important element in a data model is a hierarchy. Now hierarchies are not required, but they can make it easier to explore the data in your model. So we'll spend some time adding and using a hierarchy also.
Improving the Data Model for Reporting Hi, I'm Stacia Varga, and welcome to another module in the Modeling Data in Power BI course. So far, we've done a fair amount of work in preparing our model for reporting, but we're not done yet. There's definitely still more to do. This time our emphasis is on how to improve the data model specifically for reporting. That means we're going to spend time on certain elements of the data model that affect the user's experience with reports, whether the user is a developer or a viewer of a report. First, there are several properties in the model that affect tables and objects and ultimately control the experience of the report development process in general, as well as specific Power BI behaviors or visualizations that viewers of your report will see, as well as specific Power BI behaviors with visualizations. Then for certain types of data, we can assign fields to data categories, and that affects how Power BI displays that data in a report. And then the last model enhancement specific to reporting relates to the Power BI Q and A feature, which is a way to create a visualization by asking a question rather than using drag and drop to add selected fields to your visualization.
Working with Dates and Time in Power BI Hi. I'm Stacia Varga here with another module in the Data Modeling for Power BI course. This module continues on from the previous module, which introduced the DAX language, but this time we use DAX to work with dates and time in Power BI. DAX provides a lot of different functions related to dates and time. Analyzing information by data is probably one of the most common activities I see people need to do in Power BI, but a lot of times the datasets that they are using do not come prepackaged with a Date table. But that's okay because we have DAX to help us with that. DAX can generate tables including a calculated Date table. By having a single table as a source for dates, you'll find it easier to compare date-related measures that come from different tables in your model. Next, we explore a few of the DAX functions available for working with date and time in different ways. We can also use DAX to create calculated columns as I covered in the previous module, but this time we'll use DAX to create some columns for the calculated Date table to give us more options for analyzing data across time, and then I'll also show you how to add a calculated column to sort months correctly. And we wrap up this module by looking at how to use DAX to apply time intelligence to our data model. If you don't know what time intelligence even means, stay with me through this module, and I will explain. Time intelligence is useful for almost any form of analysis that you need to do, so it's an important concept to grasp, and DAX makes it easy to implement.
Applying Evaluation Context Hi. I'm Stacia Varga, and welcome to the Data Modeling for Power BI course and the current module, Applying Evaluation Context. In other modules of this course, I have alluded a few times to the effect of filter context on the results of DAX expressions. Now's the time that I'm going to delve more into this topic, as well as other types of contexts that influence how Power BI returns a value for a calculation. Evaluation context is an umbrella term that describes the effect on query results that are created by row context as one effect, and I'll show you what that means with some concrete examples in this module. It makes sense when you see it, but just to hear a definition of row context isn't always helpful. Row context is a very important concept for DAX, but you needed to build up some experience working with DAX in general before turning your attention to it. And if you've successfully completed the other modules in this course, you're ready to tackle row context. Evaluation context also includes the effect of filter context. So we're going to spend some time exploring that concept further, as well, as it's very important to understand in order to get the right behavior from your calculations, especially because you need to factor in both filter and row context. Last, we'll apply these concepts in a slightly different way for a special type of data structure known as a parent-child hierarchy. And we'll learn how to work with functions that are built to handle this type of data.