POWER QUERY AND DATA PROFILING

Contents
Basic Table Transformations in Power Query .................................................... 2
1. Remove Columns ..................................................................................... 2
2. Remove Rows .......................................................................................... 2
3. Rename Columns..................................................................................... 2
5. Sort Rows ................................................................................................ 3
6. Filter Rows ............................................................................................... 3
7. Split Column ............................................................................................ 3
8. Merge Columns ........................................................................................ 3
9. Group By .................................................................................................. 3
10. Pivot / Unpivot ........................................................................................ 3
11. Add Custom Column .............................................................................. 4
12. Replace Values ...................................................................................... 4
13. Transpose Table ..................................................................................... 4
14. Use First Row as Headers ....................................................................... 4
Applied Steps Panel ..................................................................................... 4
Data Profiling in Power BI ................................................................................ 5
1. Column Quality ........................................................................................ 5
2. Column Distribution ................................................................................. 6
3. Column Profile ......................................................................................... 7
How to Enable These in Power Query ............................................................ 7
Text Tools ....................................................................................................... 8
1. Format ..................................................................................................... 8
2. Extract ..................................................................................................... 8
3. Split Column ............................................................................................ 9
4. Replace Values ........................................................................................ 9
5. Merge Columns ...................................................................................... 10
6. Length ................................................................................................... 10
7. Detect Data Type .................................................................................... 10

1|Page
 POWER QUERY AND DATA PROFILING

Basic Table Transformations in Power Query

Power Query is the tool you use to clean, shape, and transform your data
before loading it into your model. Understanding basic table
transformations is essential for effective data preparation.

1. Remove Columns
Use: Delete unnecessary columns.
• How: Right-click on column → Remove
• Example: Remove "Customer Notes" column that's not needed for
analysis.

2. Remove Rows
Use: Eliminate unwanted rows (top, bottom, duplicates, blanks).
• Options:
o Remove Top Rows
o Remove Bottom Rows
o Remove Duplicates
o Remove Blank Rows
• Example: Remove the first 5 header rows in an Excel file import.

3. Rename Columns
Use: Make column names meaningful and clean.
• How: Double-click column header or right-click → Rename
• Example: Rename “Col1” to “Customer Name”
4. Change Data Types
Use: Assign correct data types (Text, Number, Date, etc.)
• How: Click data type icon in the column header.
• Example: Change a “Sales Date” column to Date type.

2|Page
 POWER QUERY AND DATA PROFILING

5. Sort Rows
Use: Sort data ascending or descending by a column.
• How: Click column header → Sort Ascending / Sort Descending
• Example: Sort “Order Date” from newest to oldest.

6. Filter Rows
Use: Keep only rows that meet specific conditions.
• How: Click filter icon in column header.
• Example: Filter “Region” column to show only “West”.

7. Split Column
Use: Split a column into two or more based on a delimiter.
• How: Select column → Split Column > By Delimiter
• Example: Split "John, Doe" into “First Name” and “Last Name”.

8. Merge Columns
Use: Combine two or more columns into one.
• How: Select multiple columns → Merge Columns
• Example: Combine “City” and “State” into “City, State”.

9. Group By
Use: Summarize or aggregate data by a column.
• How: Home > Group By
• Example: Group by “Region” and sum “Sales”.

10. Pivot / Unpivot

• Pivot Column: Convert rows into columns.
• Unpivot Columns: Convert columns into rows.
Example:
• Pivot: Convert "Month" values into columns with sales.
• Unpivot: Convert "Jan", "Feb", "Mar" columns into a “Month” and
“Value” column.
3|Page
 POWER QUERY AND DATA PROFILING

11. Add Custom Column

Use: Create a new column using a formula.
• How: Add Column > Custom Column
• Example:
• if [Sales] > 1000 then "High" else "Low"

12. Replace Values

Use: Replace specific values in a column.
• How: Right-click column → Replace Values
• Example: Replace "N/A" with null.

13. Transpose Table

Use: Flip rows into columns and vice versa.
• How: Transform > Transpose
• Example: Useful when headers are in rows instead of columns.

14. Use First Row as Headers

Use: Promote the first row to become column headers.
• How: Home > Use First Row as Headers

Applied Steps Panel

Power Query records every transformation step in the “Applied Steps”
pane (on the right side). You can click on any step to view the data at that
stage, and undo/edit steps as needed.

4|Page
 POWER QUERY AND DATA PROFILING

Data Profiling in Power BI

In Power BI, the Power Query Editor provides data profiling tools that
help you better understand and clean your data before loading it into your
data model. These tools include:
1. Column Quality
2. Column Distribution
3. Column Profile

1. Column Quality
Shows the quality of data in each column based on three metrics:
• Valid: Values that conform to the expected data type (e.g., numbers
in a number column).
• Error: Values that generated an error (e.g., divide by zero, or invalid
transformations).
• Empty: Null or missing values.
Example:

Product ID Valid Error Empty

101

102

Null

#ERROR

In Power Query, you’ll see something like:

Column Quality: 75% valid, 25% empty, 0% error

5|Page
 POWER QUERY AND DATA PROFILING

2. Column Distribution
Provides a visual representation of the distribution of values in a column.
• Shows the number of distinct and unique values.
• Displays a small bar chart to visualize value frequencies.
Example:
If a "Region" column has the following values:

Region

North

South

North

East

South

Column Distribution may show:

• Distinct values: 3 (North, South, East)
• Unique values: 1 (East, which appears only once)
• A bar chart showing:
o North (2 bars)
o South (2 bars)
o East (1 bar)
This helps spot skewed data, duplicates, or outliers.

6|Page
 POWER QUERY AND DATA PROFILING

3. Column Profile
Gives a detailed statistical summary of a selected column.
Includes:
• Count, Error, Empty
• Distinct, Unique
• Data type breakdown
• Min, Max, Average (for numeric columns)
• Value distribution graph
Example (for a "Sales" column):
Count: 100
Empty: 5
Error: 0
Min: 10
Max: 10,000
Mean: 550
Standard Deviation: 123
Value Distribution: (bar chart)
This is the most comprehensive tool for analyzing a single column’s
values.

How to Enable These in Power Query

1. Open Power BI > Transform Data (to open Power Query).
2. On the top ribbon, go to View.
3. Enable:
o Column Quality
o Column Distribution
o Column Profile

7|Page
 POWER QUERY AND DATA PROFILING

Text Tools
In Power BI Power Query, Text Tools help you clean, transform, and
manipulate text (string) data in your columns. These tools are essential for
preparing data before it's loaded into your model.

1. Format
Applies common text formatting operations.

Option Description Example Result

Converts all text to

UPPERCASE "hello" "HELLO"
uppercase

Converts all text to

Lowercase "HELLO" "hello"
lowercase

Capitalize Each "John

Capitalizes each word "john doe"
Word Doe"

Removes leading/trailing
Trim " Hello " "Hello"
spaces

Removes non-printable
Clean "He\u0001llo" "Hello"
characters

2. Extract
Extracts parts of the text string based on position or delimiter.

Option Description Example Result

First Gets the first n

"AB1234" → 3 chars "AB1"
Characters characters

Last Gets the last n

"AB1234" → 3 chars "234"
Characters characters

8|Page
 POWER QUERY AND DATA PROFILING

Option Description Example Result

Extracts a
From index 2, length 3 in
Range substring from "wer"
"Power BI"
position

Text Before Gets text before a

"user@[Link]" "user"
Delimiter character

Text After Gets text after a

"user@[Link]" "[Link]"
Delimiter character

3. Split Column
Splits text into multiple columns based on a rule.

Split By Description Example Result

Splits using a
"John" /
By Delimiter character (e.g., "John,Doe"
"Doe"
comma)

By Number of "12" / "34" /

Every n characters "123456" split by 2
Characters "56"

"ABC" /
Specific character Split at 3rd and 6th:
By Positions "123" /
positions "ABC123XYZ"
"XYZ"

4. Replace Values
Replaces specific text with new text (useful for cleaning categories or
typos).
Example:
• Replace "NYC" with "New York"
• Replace "N/A" with null

9|Page
 POWER QUERY AND DATA PROFILING

5. Merge Columns
Combines multiple columns into one with a delimiter.
Example:

First Name Last Name

John Doe

Merged with space delimiter → "John Doe"

6. Length
Calculates the number of characters in a text field.
Example:
• "Power BI" → 8

7. Detect Data Type

Automatically sets the correct data type (e.g., Text, Number, Date).

Example Workflow
Suppose you have a column: " JOHN_DOE@[Link] "
You might apply:
1. Trim → "JOHN_DOE@[Link]"
2. Lowercase → "john_doe@[Link]"
3. Extract Text Before Delimiter '@' → "john_doe"
4. Replace Underscore with Space → "john doe"
5. Capitalize Each Word → "John Doe"

10 | P a g e