Profiling the Data
Troubleshooting in Power Query
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
You might need to change some settings related to your data source after you'd already connected to it. Suppose you move the file imported to a different folder. You would then have to update the file path in Power BI to continue working with this file and to allow you to refresh data in Power BI. I will refer to our current example. The first option is to make these changes in Power Query. To do this select Transform data in the Power BI desktop. This will open the Power Query interface. You could change the data source settings in the cog wheel next to the Source step under APPLIED STEPS in Query Settings. Select the cog wheel, change the file path, and also change the file type if that is required.
This approach is problematic, as you would then need to change the settings in each query that references the file. This could be time-consuming and could result in errors. The preferred approach would be to change the data source settings by selecting Data source settings on the Home tab. To do this, in Power BI desktop, select File, then Options and settings, and then Data source settings. This allows you to change the source settings for all affected queries at the same time. You simply select Change Source and then include the new location of the file here. You can also change or clear the permissions by selecting Edit or Clear Permissions respectively. Permissions cover the privacy level and credentials used for connecting to a data source.
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.