Profiling the Data
Troubleshooting in Power Query
The course is part of this learning path
In this course, we’ll review the Power BI Desktop interface. Then, we’ll show you how to load data into Power BI Desktop and how to save your file. We will also explain data profiling and look at the various data profiling options in Power Query like column quality, column value distribution and column profiling, and the benefits of using these.
Lastly, we will look at how to resolve cell-level errors, empties, and inconsistencies in Power Query. This section will cover how to replace errors, replace values, remove rows, and how to identify the root cause of the error using Power Query. The demos in this course will provide you with practical examples that will help you to troubleshoot when encountering issues while loading data into Power BI.
- Understand how to load data into Power BI and how to optimise functionality and size by using data profiling
- Understand how to resolve errors, empties, and data inconsistencies in Power Query
- Anyone who would like to learn about importing data into Power BI
- Anyone who needs to resolve cell-level errors or empties in a Power BI model or who would like to understand data profiling to improve the functionality of their model
- Some basic knowledge of, or experience in, working with large datasets
- Some experience with Power BI (not mandatory)
The files used in this course can be found in the following GitHub repo: https://github.com/cloudacademy/loading-data-power-bi
Hello, and welcome to this course, Introduction to Loading Data into Power BI. My name is Bianca Burger. I'm a finance business partner and use Power BI to create useful, interactive dashboards to analyze financial metrics. Feel free to connect with me using the link on the screen. If you have any questions about this course or any Cloud Academy courses, you can direct these queries to firstname.lastname@example.org.
A prerequisite for this course is some basic knowledge of, or experience in, working with large data sets. When you have completed this course, please take a moment to rate it. Your feedback is valued, and allows us to deliver quality courses to you.
Let's start with an overview of the Power BI products. You might've heard the following terms. Power Query, Power View, Power BI Desktop, Power Pivot, Power Map, or Power BI Mobile. You might also be thinking, what's the difference? Which one of these Power products should I be using? The Power BI products listed above are all a family of products built on shared engines. As mentioned, it can, however, initially be very confusing to understand how these products all fit together. To simplify this visually, I've included the next illustration to explain how the products link together. You might want to refer back to this picture as you learn, as I find it's a useful way to make sense of it all.
First, you have the raw data or inputs. This would be the files that you link Power BI to, for example, Excel files or maybe a SQL server. Next up is the Power Pivot. You always use a Power Pivot in Power BI. Sometimes you might not even be aware of this. Power Pivot is the central engine that powers all the BI solutions. It's also known as the DAX Engine or the Brain. From here, we move into the visuals. This could be dashboards, charts, pivots, and usually we use Power BI dashboard to display these. Quite often, the data you use needs to be shaped, transformed, or cleansed first. That's where the Power Query comes in.
So, as you can see, we start with raw data. Raw data then moves into the Power Query, where data is transformed or cleansed. Thereafter, this moves into the Power Pivot. And from there, the information flows to the data visualizations or dashboards. Power BI dashboards can then be shared to other users on Power BI or viewed on Power BI Mobile. Before you can analyze data in Power BI and do exciting things like create dashboards, you first need to load raw data into Power BI. Data can be loaded from various sources, and currently, Power BI allows for data to be loaded from over 100 different source types.
This course will cover how to get, check, and fix data, when loading data into Power BI, and is therefore very useful if you are starting your journey in using Power BI, or need to understand how to use Power Query to check your data loads and fix errors. To get data, we will review how to connect Power BI to a data source. To check data, we will look at how to use the data profiling tools in Power Query. To fix data, we will look at troubleshooting common data loading errors in Power Query. Okay, let's get started.
Bianca is a chartered accountant and finance business partner who works with Power BI regularly to create useful, interactive dashboards to analyze financial metrics. She has worked as a lecturer and as a financial analyst in FMCG companies assisting sales and marketing teams with reviewing and understanding their financial results and forecasts, and identifying risks and opportunities for improvement. Bianca enjoys using technology to automate and simplify financial metrics.