POWER BI
Tutor: Nguyễn Lê Chí Bảo
01 03
Basic Concepts Data Model
MODULE
02 04
Transform the data DAX
MODULE 2.
TRANSFORM DATA
Module 2:TRANSFORM DATA
Introduction to Power Query Editor
Power Query interface
Transform data
Introduction to Power Query Editor
Module 2:TRANSFORM DATA
Data cleansing and transformation?
• Data cleansing is a process of removing and detecting inaccurate records from a
recordset. It is used to alter data in a given storage resource to make sure that it is accurate
and correct.
• Transformation is a process of filtering out unusable records of the database in Power BI
desktop. The data which is not useful to make a report and analysis, this type of data should
be cleaned from our database.
Module 2:TRANSFORM DATA
What is Power Query?
Power Query is Microsoft’s Data Connectivity and Data Preparation technology. It basically,
enables business users to access data stored in data sources seamlessly whilst, reshaping it to
fit their needs. It’s easy to use, engaging, even convenient to use for the no-code users.
Power BI Desktop comes equipped with Power Query Editor. You can use the Power Query
Editor to connect to one or many data sources, shape and transform the data. You could modify
the data in hand to meet your needs, make it more usable, and then load that model into Power
BI Desktop.
Module 2:TRANSFORM DATA
What is Power Query?
Power Query Interface
Module 2:TRANSFORM DATA
Power Query Interface
Module 2:TRANSFORM DATA
Power Query Interface
Module 2:TRANSFORM DATA
Power Query Interface
1. Ribbon: the ribbon navigation experience, which provides multiple tabs to add transforms,
select options for your query, and access different ribbon buttons to complete various tasks.
2. Queries pane: a view of all your available queries.
3. Current view: your main working view, that by default, displays a preview of the data for your
query. You can also enable the diagram view along with the data preview view. You can also
switch between the schema view and the data preview view while maintaining the diagram
view.
4. Query settings: a view of the currently selected query with relevant information, such as
query name, query steps, and various indicators.
Transform Data
Module 2:TRANSFORM DATA
Type of Transformations
• Combining data (merge, append, join)
• Modifying columns (remove, split, create, rename, fill values down)
• Modifying rows (promote header, remove rows, remove duplicates)
• String manipulation (uppercase, replace values, trim)
• Other (pivot, unpivot, parse JSON/XML)
Module 2:TRANSFORM DATA
Modifying columns - Remove
Module 2:TRANSFORM DATA
Modifying columns - Remove
Do one or more of the following:
• To remove a single column, select the column you want to remove, and then
select Home > Remove Columns > Remove Columns.
• To remove several columns, select the columns by using Ctrl + Click or Shift + Click.
The columns can be contiguous or discontiguous.
• To remove all columns except the selected column, select one or more columns, and
then select Remove Other Columns.
Module 2:TRANSFORM DATA
Modifying columns - Split
Module 2:TRANSFORM DATA
Modifying columns - Split
• Split a column by delimiter: split a column with a text data type into two or more columns by
using a common delimiter character
• Split a column by number of characters: split a column with a text data type into two or
more columns by using the number of characters within a text value.
• Split a column by positions: split a column by specifying fixed numeric positions of
characters.
• Split a column by letter case combinations: split a column based on case letter
combinations, lowercase to uppercase or uppercase to lowercase
• Split a column by digit and non-digit combinations: split a column based on digit and non-
digit combinations, digit to non-digit or non-digit to digit.
Module 2:TRANSFORM DATA
Modifying columns - Rename
• To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data,
and then select Query > Edit.
• Select a column, and then select Transform > Rename. You can also double-click the column
header.
• Enter the new name.
Module 2:TRANSFORM DATA
Modifying columns - Remove
Module 2:TRANSFORM DATA
Modifying columns - Transform
Module 2:TRANSFORM DATA
Modifying columns – Create column
Module 2:TRANSFORM DATA
Modifying rows – Promote header
• To open a query, locate one previously loaded from the Power Query Editor, select a cell in the
data, and then select Query > Edit.
• Do one of the following:
+ To promote the first row to column headers, select Home > Use First Row As Headers.
+ To demote column headers to the first row, select Home, select the arrow next to Use First
Row As Headers, and then select Use Headers as First Row.
Module 2:TRANSFORM DATA
Modifying rows – Promote header
Module 2:TRANSFORM DATA
Modifying rows – Remove rows
Module 2:TRANSFORM DATA
Modifying rows – String manipulation
Module 2:TRANSFORM DATA
Combining data – Merge
You need at least two queries that can be merged and that have at least one or more columns to
match in a join operation. They can come from different types of external data sources.
• To open a query, locate one previously loaded from the Power Query Editor, select a cell in the
data, and then select Query > Edit.
• Select Home > Merge Queries. The default action is to do an inline merge. To do an
intermediate merge, select the arrow next to the command, and then select Merge Queries as
New.
The Merge dialog box appears.
• Select the primary table from the first drop-down list, and then select a join column by selecting
the column header.
• Select the related table from the next drop-down list, and then select a matching column by
selecting the column header.
Module 2:TRANSFORM DATA
Combining data – Merge
Module 2:TRANSFORM DATA
Combining data – Merge
• After you select columns from a primary table and related table, Power Query displays the
number of matches from a top set of rows. This action validates whether the Merge operation
was correct or whether you need to make changes to get the results you want. You can either
select different tables or columns.
• The default join operation is an inner join, but from the Join Kind drop down list, you can
select the following types of join operations
• If you want to do a fuzzy match, select Use fuzzy matching to perform the merge and select
from the Fuzzy Matching options. For more information, see Create a fuzzy match.
• To include only those rows from the primary table that match the related table, select Only
include matching rows. Otherwise, all rows from the primary table are included in the resulting
query.
• Select OK.
Module 2:TRANSFORM DATA
Combining data – Append
• To open a query, locate one previously loaded from the Power Query Editor, select a cell in the
data, and then select Query > Edit.
• Select Home > Append Queries. The default action is to do an inline append. To do an
intermediate append, select the arrow next to the command, and then select Append Queries
as New.
• Decide the number of tables you want to append:
+ Select Two tables, and then select the second table in the drop down list box to append.
+ Select Three or more tables. From the Available tables box, add the tables you want to
append to the Tables to append. Use the arrows on the right of that box to change sequence.
• Select OK.
Module 2:TRANSFORM DATA
Combining data – Append
Module 2:TRANSFORM DATA
Practice
At AdventureWorks_Customer table:
• Create new column: Full Name based on 3 columns: Prefix, FirstName and LastName. Example:
‘MR. JON YANG’
• Create new column: UserName from EmailAddress column. Example: jon24; eugene,..
• Create new column: Domain from EmailAddress column. Example: adventure-works
At AdventureWorks_Product table:
• Create new column: SalePrice. SalePrice = ProductPrice*0.8
• Append three tables: AventureWorks_Sales_2015, 2016, 2017 => AventureWorks_Sale
• Merge two tables: AventureWorks_Sale vs AventureWorks_Product
THANKS
CREDITS: This presentation template was created by
Slidesgo, including icons by Flaticon, and Name: Nguyễn Lê Chí Bảo
infographics & images by Freepik and illustrations Email: bao2101998@gmail.com
by Storyset Phone: 091 544 2420
Linkedin: nguyenlechibao
