Validate data in Power BI

00:03

When working with dashboards based on data exchanges within Power BI,

00:08

it is important to ensure that the information is accurate,

00:11

and that the quantities and elements correctly reflect the data exchanges.

00:15

This includes verifying data types and counting specific categories of elements in the exchange to compare with the dashboard.

00:23

First, check the format of the data.

00:27

From the Data pane, expand one of the loaded tables and select one of the column headers.

00:32

Then, from the contextual ribbon, review the Data type field.

00:39

From the sidebar, open the Table view to see the values of each row within each column.

00:46

Select each column to display and review its Data type,

00:49

and verify that each column is correctly classified.

00:53

Misclassification, such as something labelled text instead of a number, can lead to incorrect information.

01:01

This is especially important when quantifying numbers of elements or summarizing specific values.

01:07

From the Data pane, select a column with a sum symbol adjacent to its name to see how the data will be treated.

01:14

Review this information from the ribbon, in the Properties panel.

01:19

Expand the Summarization drop-down, if needed, to choose how to summarize the selected data.

01:26

You can also validate information in the Power Query editor.

01:30

Right-click any column in the Data pane and select Edit Query.

01:35

In the Power Query window, icons display in each column header to indicate the data type, such as text or decimal numbers.

01:44

To change a data type, right-click the column header, select Change Type, and then choose the appropriate data type.

01:53

To review how much information is filled in within a column,

01:56

observe the bar below the column header name.

01:59

For example, if there are rows with a NULL value, the bar does not extend across the entire header.

02:07

Here, in the Name column, only about one-sixth of the information is filled in.

02:13

To review the number of rows, on the Transform tab of the ribbon, from the Table panel, click Count Rows.

02:21

This adds a step in the Query Settings panel, in the Applied Steps group.

02:26

You also see the number of rows in the table,

02:29

which you can compare against the values on your dashboard.

02:33

Remove the Counted Rows step before returning to your dashboard

02:37

to avoid displaying information as a single number.

02:40

You can apply different filters and use Count Rows again to determine the quantity of specific information within the data.

02:48

For example, to check the number of elements for a particular family name,

02:53

expand the Family Name column header, deselect Select All to deselect all elements,

02:59

then select the desired family name, such as Recess Lab Linear LED.

03:05

Click OK, and then click Count Rows.

03:10

As you can see, there are 77 rows containing filtered information.

03:16

If needed, in the Applied Steps, you can click Filtered Rows to review what was filtered.

03:23

Before returning to the dashboard to compare the information with the results,

03:27

remove the Counted Rows step.

03:30

On the ribbon, click Close and Apply, and then open the Home tab.

03:35

The dashboard now displays only information about that specific family name.

03:40

To display all of the information on the Dashboard again,

03:44

open the Power Query window and remove the previously applied filter by deleting the Filtered Rows step.

03:51

As a reminder, if all your data is not visible on the dashboard,

03:55

always review the Applied Steps,

03:57

as filtering may prevent the dashboard from displaying all the data.

04:01

Click Close and Apply to return to the dashboard.

04:06

Removing this filter reloads the data, and now all information is visible from all family types.

04:12

As a best practice, always check for correct data types in your visuals

04:17

and validate the information in multiple ways via the Query Editor,

04:21

as well as the original data file, to ensure accuracy.