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
Now it's time to get data. So, suppose you wanted to load financial data from an Excel file into Power BI. To do this, we select the Get Data button at the top of the screen. Then, there are various data sources from which you can select data. The first one is Excel. Then, there's Power BI datasets, Power BI dataflows, and also a SQL server. At the bottom of the list, you can select More Sources, and you can see here the options available, including an access database.
Currently, there are over 100 data source options available to select from in Power BI. For this example, I will just be selecting data from an Excel file. Locate the file on your computer, select it, and press Open. Now wait for it to load. This then loads all the tabs within the file. You can select them all. The first one I have is Financials. So, you can see all the columns are reflected here in the preview. I can scroll to the right and do a quick check to see that all the columns are pulling through.
Next, I select Financial Periods. And then the Promotional Groups tab. Once done, I press Load. It could take some time to load, depending on the size of your file. When you look at the fields view to the far right of the screen, you can see that all the tabs in the Excel file have been loaded. If I then select Financials, I can see that all the columns in that table is reflected here, and there are quite a few columns.
Next, I select Financial Period. I can see that it's also all been loaded. And then lastly, I will select Promotional Groups and that's also looking good. Next, I will move to the left to the viewing pane. There are three viewing options. At the top is report view. Currently, there's nothing reflected here, but this is where you can build your dashboards. There's a range of data visualization options to pick from in Power BI. Then there is the data view. At the moment, I have financial year actual selected. This is where you can preview the data that has been loaded, and you can see that everything has pulled through. And then lastly, there's model view. Each one of the three tables are reflected here. And, at a later stage, I will show you how to build relationships between these tables.
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.