The main goal for data analysts is to make sense of data and express findings to respective stakeholders. The analyst imports into Power BI from different sources, upon examining it is not prepared for analysis.
Simple mistakes such as columns with string data types containing numeric rows, columns containing errors and null values, duplicated unique identifiers can make the data unusable.
Data Cleaning and Preparation
Power BI offers Power Query which is powerful for preparing and cleaning data. Clean data is beneficial for:
- Producing more accurate results when performing calculations
- Finding data more intuitively due to better organization
- Simpler navigation and effective use of slicers.
Steps for cleaning data
- First step is transforming your imported data to ensure it is suitable for use. Open Power Query editor by clicking on transform data in the queries section of the menu.
Power Query will only modify a view of your data and not your original data.
- Next is to identify column headers and names. In the case of the example below, column names are non-descriptive making it hard to understand what each column represents.
When you go to transform data, the correct column names are displayed as the first row. Select the table home icon as shown and on the dropdown, click on Use first Rows as header.
Its also possible to change the header from the menu.
Now the correct headers will be displayed
Verify that the column names are correct by removing spelling errors, and ensuring the naming convention is user friendly. In this case column names are capitalized, if using snake case or any other convention, ensure all columns are consistent.
To rename a column, right click the column name and select rename. In this case we can rename the Location column to Order Location.
You can also remove any column you find unnecessary by going to
Every time you make a change when transforming data, it will be displayed on the properties tab.
You can easily undo a change by clicking on the X.
Duplicates
Remove duplicates from columns to only keep unique names for selected columns. We have identified that each transaction should be unique and no two transactions can share a transaction id. In this example, we remove duplicate transactions by right clicking transaction id and selecting remove duplicates.
Incorrect data types
When importing data, some column data types are incorrectly interpreted such as numeric data saved as text. In this case, data formatted on the left implies it is text while numeric data will be formatted on the right.
When you click a specific column name the data type will also be displayed in the menu as follows
Click on the dropdown to select the appropriate data type.
Incorrect data types can prevent you from creating hierarchies, performing calculations, or forming proper relationships with other entities.
In this example we transformed the following columns data types into whole numbers, decimal and date types
Replacing nulls, Errors and blanks
These types prevent you from performing calculations and summarization on data.
Power query uses find and replace to find the values in a row you want to replace and change to the specified value.
Replace errors and blanks with unknown.
Use column distribution in the view tab to detect anomalies in your data.
Once you are done with cleaning and preparing your data in power query, you can click on close and apply.
Data Analysis Expressions
These are functions and operators that can be combined to build formulas in Power BI
Common DAX Functions
Aggregation Functions
SUM - adds all the numeric values in a given column and returns a decimal number.
= SUM(column)
We can find the total revenue by summing all orders
AVERAGE - Returns the average mean of all numeric values in a column. Null values may cause the average to result in an error.
AVERAGE(column)
In an example you can calculate the average of customers loyalty score as shown
The above is achieved by creating a new measure which is a dynamic calculation on data that is created using DAX.
We can view the result of the measure using a card in report view.
COUNT - Used to count the number of rows in the specified column which and NON-BLANK
COUNTROWS - This is used when you want to count the number of rows in the specified table defined by an expression
Total Electronic Products =
CALCULATE(
COUNTROWS(Products),
Products[Category] = "Electronics"
)
The above COUNTROWS example returns the count of only products in the electronics category
SUMX - evaluates the expression for each row in a table then returns the sum. It is referred to as an iterator function and only counts numeric rows.
SUMX(
Table,
Expression
)
Filter Functions
Powerful functions that let you manipulate data context
CALCULATE - used to evaluate an expression
FILTER - returns a subset of a different table as a new table
INDEX -
RANGE
RANK
Date and Time Functions
CALENDAR - returns a new table with a single column of a set of dates.
= CALENDAR (DATE (2024, 7, 1), DATE (2026, 7, 31)
DATE - returns the date in datetime format.
DATE(yyyy, mmm, dd)
DATEVALUE - converts text value to date in datetime format.
Dashboards in Power BI
Dashboards are used in power bi to tell a story about the data through visualizations. It highlights the important details that can be reviewed in the report
Here is a simple example of a dashboard
Conclusion
To get the most out of data we need to transform it, perform calculations and create intuitive dashboards that summarize all the insights from the data. Power BI is a powerful tool to achieving this. Understanding and following the right steps will help data analysts to analyze data efficiently.
Top comments (0)