1. Home
  2. Training Library
  3. Big Data
  4. Courses
  5. Loading Data into Power BI

Loading Data into Power BI - Conclusion

Contents

keyboard_tab
Course Introduction
Troubleshooting in Power Query
Start course
Overview
Difficulty
Intermediate
Duration
40m
Students
56
Ratings
5/5
starstarstarstarstar
Description

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.

Learning Objectives

  • 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

Intended Audience

  • 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

Prerequisites

  • Some basic knowledge of, or experience in, working with large datasets
  • Some experience with Power BI (not mandatory)

Resources

The files used in this course can be found in the following GitHub repo: https://github.com/cloudacademy/loading-data-power-bi 

Transcript

Let's do a quick recap of everything we have learned in this course. We had a look at the power BI desktop interface, where we reviewed the ribbon, the get data button, the view selector with report view, data view and model view, the field section, the visualization section, and the save button. In the view selector, report view is where you include your dashboards. Data view is where you review the data imported in Power BI. Model view is where you review the relationship between tables imported into Power BI.

We went through these steps for loading data into Power BI desktop. You select the get data button, select the source type like Excel, locate and select the file from which to load data and press load. Then we did a quick review of how to save the file. Press save in Power BI desktop, select the file location, name the file and press save. Note that Power BI files are saved as a .pbix file type. We also covered how to change data source settings. If you change the location of a file, you can change this as follows.

In Power BI desktop, select file, then options and settings, and then data source settings. Select change source, and then include the new location of the file here. The next section we covered was data profiling. This is useful for understanding your data. In Power BI desktop, select transform data. Then in power query, select view. We covered the following options in the power query view section. Column quality, column distribution and column profile. Column quality showed the number of valid, error and empty cells in a column. Column distribution, reflected the distribution of values in a column and the number of distinct and unique cells in a column. The distinct number refers to how many different values there are in a column, once duplicates are excluded. The unique number, shows how many values occur exactly once.

We also discussed cardinality and that we want to achieve low cardinality columns. And to do this, what the number of distinct and unique items in a column to be low. Column profiling shows the column statistics and the value distribution. The value distribution shows further information on the column distribution and spread.

Lastly, we looked at how to resolve cell level errors, empties and inconsistencies that could occur when loading data into Power BI. We covered the following functions. Replace errors, replace values, remove rows, and identify the root cause of the error. An important point was that cell level error values don't prevent queries from loading, but the error values are loaded as blank values. We covered key things that result in cell level errors or empties, which are invalid cell values like N/As, null values and data type conversion errors.

To replace errors, replace values, or remove rows, you right-click the relevant column in power query and select replace errors, replace values, or remove rows. To identify the root cause of the error, select the error cell in power query and review the error message in the preview section at the bottom of the screen.

About the Author
Avatar
Bianca Burger
Chartered Accountant and Finance Business Partner
Students
51
Courses
1

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.