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

Interrogating Data Statistics

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

To better understand your data, you can also review the Column statistics. This can be viewed by selecting Column profile in Power Query. If you select Column profile, you will see Column statistics and Value distribution appear. In Column statistics, you can see statistics like Count, Error, Empty, Distinct, Unique, Zero, Min, Max, Average, Standard deviation, Even, and Odd. The Column statistics provided is different for number and text columns.

I currently have the Product ID column selected, which is a numbers column. But I will now select a text item to show you the differences in a text column. You can see whether a column is numbers or text by referring to the symbol to the left of the column name. I will choose the text column, PromoGroups. This shows Count, Error, Empty, Distinct, Unique, Empty string, Min, and Max. Statistics like Average, Standard deviation, Even, and Odd, are not reflected for text columns, as it's only applicable to numbers. But in text columns, you can see number of empty strings, which is not reflected in number columns.

This is all very useful to help you understand your data at a quick glance. You can copy the Column statistics by clicking on the ellipsis next to Column statistics, and selecting Copy. Then, paste this into your Excel sheet. Under Value distribution, you see further information on the Column distribution and spread. It also now shows which column refers to which value. Ice cream appears most frequent. When you hover over the column, it shows that ice cream appears 13 times, and pound cakes appears six times.

When you click on the ellipsis, you have an option on how to group the values, depending on data type. Text values, in this example, can be grouped by value or text length. It's currently grouped by value, but if I select text length, you can see the graph changes to reflect this.

About the Author
Avatar
Bianca Burger
Chartered Accountant and Finance Business Partner
Students
57
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.