Power BI

Power BI Query Editor

In Built ‘Text
Transformation’
◦ In built ‘Text Transformations’ are:
1. Format
2. Extract
3. Split Column
4. Merge Columns
 Text Transformation:
‘Transform’
from ‘Transformation’ or
Tab
‘Add Column’ tab
◦ Text Transformation tools are available in
both tabs ‘Transform’ as well as ‘Add
Column’
◦ Text transformation tools under ‘Transform’
tab is in ‘Text Column’ group.
◦ While text transformation tools under ‘Add
Column’ is in ‘From Text’ group.
◦ Use of tools from ‘Add Column’ gives result
on a new column, while use from
‘Transform’ tab operates and gives result on
‘Add Column’ same column’

Tab
Text Transformation – ‘Format’
◦ lower case: Makes everything to lowercase from all selected column or
columns.
◦ UPPER CASE: Makes everything to UPPER Case from selected
column or columns.
◦ Capitalize Each Word: Capitalizes each word from selected column or
columns.
◦ Trim: Removes spaces at the beginning, removes spaces at the end and
makes one space from multiple spaces anywhere else in selected column
or columns.
◦ Clean: Removes non printable characters from selected column or
columns.
◦ Add Prefix: Add something you want before text in selected column or
columns.
◦ Add Suffix: Add something you want to the end of text in selected
column or columns.
 ◦ Select ‘Product Name’ column
Format: lowercase ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’
group → ‘Format’ → ‘lowercase’
VALUES IN ‘Product Name’ COLUMN GOT
CONVERTED TO LOWERCASE
 ◦ Select ‘Product Name’ column
Format : lowercase ◦ Go to ➔ ‘Add Column’ ribbon → Click on ‘From
Text’ group → ‘Format’ → ‘UPPERCASE’
A NEW COLUMN ADDED WITH UPPERCASE
 REMOVE
‘INSERTED
UPPERCASED
TEXT’ FROM
‘APPLIED
STEPS’
 Format: ◦ Select ‘Product Name’ column
◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’

UPPERCASE group → ‘Format’ → ‘UPPERCASE’

VALUES IN ‘Product Name’ COLUMN GOT
CONVERTED TO UPPERCASE
Format: Capitalize ◦ Select ‘Product Name’ column
◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’
Each Word group → ‘Format’ → ‘Capitalize Each Word’
FIRST LETTER OF EACH WORD IS NOW
CAPITALIZED
 ◦ Select ‘Shipping Address’ column

Format: Trim ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’

group → ‘Format’ → ‘Trim’
ANY EXTRA SPACE OTHER THAN SINGLE SPACE BETWEEN
WORDS GETS REMOVED
 ◦ Select ‘Shipping Address’ column

Format: Clean ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’

group → ‘Format’ → ‘Clean’
REMOVES Non-Printable Characters IF ANY FROM SELECTED
COLUMNS
 ◦ Select ‘Invoice’ column

Format: Add Prefix ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’

group → ‘Format’ → ‘Add Prefix’
◦ Type ‘Inv-’ and Click on ‘Ok’
PREFIX ‘INV-’ ADDED TO INVOICE NUMBER AND DATA TYPE
AUTOMATICALLY CHANGED TO ‘TEXT’
 ◦ Select ‘Shipping Address’ column

Format: Add Suffix ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’

group → ‘Format’ → ‘Add Suffix’
◦ Type ‘ State’ and Click on ‘Ok’
AT THE END OF EACH ADDRESS ‘ State’ WORD IS ADDED
Extract : Text Transformation
Power BI has following ‘Extract’ text transformations -
◦ Length: Returns the length of the text as number in the selected columns. For example
‘abc’ will return ‘3’.
◦ First Character: Returns a specified number of characters from the start of each value
in selected columns. You can get first two characters from "abc" and see the result "ab"
◦ Last Character: Returns a specified number of characters from the end of each value
in selected columns. You can get last two characters from "abc" and see the result "bc"
◦ Range: Returns a specified number of characters from each value in the selected
columns. For example from "abcdef" provides two characters from the third characters -
resulting in "cd"
◦ Text Before Delimiter: Returns the text that occurs before a delimiter. A delimiter is
special character like comma, dash, bar etc.
◦ Text After Delimiter: Returns the text that occurs after a delimiter.
◦ Text Between Delimiters: Returns the text that occurs between two delimiters.
Make Duplicate of
‘Product Name’ column
◦ Select ‘Product Name’ column in ‘Row
Transformation’ table
◦ Right click on it and select option – ‘Duplicate’
YOU GET A DUPLICATE OF ‘Product Name’ COLUMN NAMED AS
‘Product Name - Copy’
 ◦ Select ‘Product Name - Copy’ column in ‘Row

Extract: Length Transformation’ table

◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’
group → Extract → Length
THIS GIVES YOU COUNT OF CHARACTER FOR EACH VALUE IN
SELECTED COLUMN
RENAME ‘PRODUCT NAME – COPY’ COLUMN TO
‘Product Name Length’
 ◦ Select ‘Product Name’ column of ‘Row
Extract: First Transformation’ table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’
Characters group → ‘Extract’ → ‘First Characters’
◦ Type ‘2’ to extract first 2 characters & Click ‘Ok’
A NEW COLUMN GETS ADDED NAMED ‘FIRST CHARACTERS’ AT
THE END
RENAME ‘FIRST CHARACTERS’ COLUMN TO
‘Product Code’
 ◦ Select ‘Customer Name’ column in ‘Row
Extract: Last Transformation’ table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’
Characters group → ‘Extract’ → ‘Last Characters’
◦ Type ‘2’ to extract last 2 characters & Click ‘Ok’
THIS GIVES YOU ‘Last Characters’ COLUMN ADDED AT THE END
RENAME ‘Last Characters’ COLUMN TO ‘Middle
Name’
Extract:
Range
Import ‘Inventory Details.xlsx’
file from data folder in Query
Editor
◦ Go to ➔ Home → New
Source → Excel
◦ Select the File name
‘Inventory Details.xlsx’
◦ Select ‘Inventory’ table and
click ‘Ok’
 ◦ Select ‘Product Code’ column in ‘Inventory’ Query table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’ group →
Extract: Range ‘Extract’ → ‘Range’
◦ Type ‘4’ to start after character & then type ‘4’ extract four
characters.
◦ Click ‘Ok’
THIS GIVE YOU A NEW COLUMN NAME ‘Text Range’ ADDED AT
THE END OF TABLE
RENAME THIS COLUMN FROM ‘Text Range’ TO
‘Product Category’
 ◦ Select ‘Shipping Address’ column in ‘Row Transformation’
Extract: Text table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’ group →
‘Extract’ → ‘Text Before Delimiter’
Before Delimiter ◦ Type comma ‘,’ as delimiter
◦ Click ‘Ok’
A NEW COLUMN ‘Text Before Delimiter’ IS ADDED AT THE END OF
QUERY TABLE
RENAME ‘Text Before Delimiter’ COLUMN TO ‘City’
 ◦ Select ‘Shipping Address’ column in ‘Row Transformation’
Extract: Text After table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’ group →
‘Extract’ → ‘Text After Delimiter’
Delimiter ◦ Type Space ‘ ’ as delimiter
◦ Click ‘Ok’
A ‘Text After Delimiter’ COLUMN IS ADDED AT THE END OF QUERY
TABLE
 ◦ Select ‘Text After Delimiter’ column in ‘Row Transformation’
Extract: Text Query table
◦ Go to ➔ ‘Transform’ ribbon → ‘From Text’ group →
‘Extract’ → ‘Text Before Delimiter’
Before Delimiter ◦ Type comma ‘,’ as delimiter
◦ Click ‘Ok’
‘Text Before Delimiter’ DOESN’T CHANGE THE NAME OF COLUMN
BUT YOU GET District VALUE IN THE COLUMN
RENAME ‘Text After Delimiter’ COLUMN TO ‘District’
REMOVE NEWLY CREATED ‘District’ COLUMN
FROM ‘Home → Remove Column’
 ◦ Select ‘Shipping Address’ column in ‘Row Transformation’
Extract: Text Query table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’ group →
‘Extract’ → ‘Text Between Delimiters’
Between Delimiters ◦ Type comma ‘,’ as Start Delimiter as well as End delimiter
◦ Click ‘Ok’
A NEW COLUMN IS ADDED AT THE END OF QUERY TABLE
NAME ‘Text Between Delimiters’
RENAME ‘Text Between Delimiters’ COLUMN TO ‘District’
 ◦ Select ‘Shipping Address’ column in ‘Row Transformation’ Query

Extract: ‘Text After

table
◦ Go to ➔ ‘Add Column’ ribbon → ‘From Text’ group → ‘Extract’
→ ‘Text After Delimiter’

Delimiter’ from end ◦ Type comma ‘,’ as delimiter & Click on ‘Advance’
◦ Under ‘Scan for delimiter’ select ‘From the end of the input’ &
Click ‘Ok’
A NEW COLUMN ‘Text After Delimiter’ IS ADDED AT THE END OF
THE QUERY TABLE
RENAME ‘Text After Delimiter’ COLUMN TO ‘State’
Remove Columns – ◦ Click on ‘City’ column to select it
◦ Press and hold SHIFT key and click on ‘State’ column header to

City, District, State

select – City, District, & State columns
◦ Click on Home ➔Manage Columns → Remove Columns
Split Column: Text Transformation
Power BI has following ‘Split Column’ text transformations –
◦ By Delimiter: Split values in the selected column based on the specified delimiter.
◦ By Number of Characters: Split values in the selected column into fragments
with the specified length.
◦ By Positions: Split values in the selected column into fragments at specified
positions.
◦ By Lowercase to Uppercase: Split values in the selected column based on
transitions from a lowercase letter to an uppercase letter.
◦ By Uppercase to Lowercase: Split values in the selected column based on
transitions from an uppercase to a lowercase letter.
◦ By Digit to Non-Digit: Split values in the selected column based on transitions
from a digit to a non-digit character.
◦ By Non-Digit to Digit: Split values in the selected column based on transitions
from a non-digit to a digit character.
Split Column: By
Delimiter
Selecting the By Delimiter option opens the following
window.
◦ Select or enter delimiter: From the drop-down list,
please select the delimiter that you want to use as the
split character. If it is not there in the list, then select
the Custom option and specify that custom character.
◦ Left most delimiter: This option split the leftmost
string before the first delimiter.
◦ Right most delimiter: This option split the right-
most string after the last delimiter.
◦ Each Occurrence of the delimiter: The text split at
each occurrence of a delimiter.
 ◦ Select ‘Customer Name’ column in ‘Row Transformation’
Split Column: By query table
◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’ group →
Delimiter ‘Split Column’ → By Delimiter
◦ Select → Split At: Left-most Delimiter, and Click ‘Ok’
 Rename two New Columns Created -
◦ Rename ‘Customer Name.1’ column to ‘Customer First
Rename Columns Name’
◦ Rename ‘Customer Name.2’ column to ‘Customer Last
Name’
Split Column: By
Number of Characters
Selecting the By Number of Characters option
opens the Split Column by Number of Characters
window.
◦ Number of Characters: Please specify the
number of characters used to split the column.
◦ Once, as far left as possible: This option split the
leftmost string before the number of characters.
◦ Once, as far right as possible: This option split
the right-most string after the number of
characters.
◦ Repeatedly: The text split for every 5 characters.
 ◦ Select ‘Product Code’ column in ‘Inventory’ query table
Split Column: By ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’ group →

Number of Characters ‘Split Column’ → ‘By Number of Characters’ → Type 3

◦ Select → Split: Once, as far left as possible; and Click ‘Ok’
 ◦ Rename column ‘Product Code.1’ to ‘Brand
Name Code’
Rename Columns ◦ Rename column ‘Product Code.2’ to ‘Product
Name Code’ respectively
 ◦ Go to ➔ Transfer → Text Column → Text
Remove ‘-’ from ‘Product After Delimiter
Name Code’ column ◦ Type ‘-’ as delimiter
THIS REMOVES ‘-’ CHARACTER FROM FIRST POSITION IN
COLUMN NAMED ‘PRODUCT NAME CODE’
Split Column: By
Positions
◦ Selecting the By Positions option opens the Split
Column by Positions window.
◦ It will show guess values to split by positions, but
you can input your values.
◦ For example split into columns after positions 0,5,7
etc.
 ◦ Select ‘Product Name Code’ column in
Split Column: By ‘Inventory’ query table
◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’
Positions group → ‘Split Column’ → ‘By Positions’
◦ Type → Positions: 0, 4, 7
YOU GET THREE COLUMNS NAME ‘Product Code Name.1’, ‘Product
Code Name.2’, AND ‘Product Code Name.3’
 ◦ Rename ‘Product Code Name.1’ column to ‘Product
Subcategory Code’

Rename Columns ◦ Rename ‘Product Code Name.2’ column to ‘Product

Category Code’
◦ Rename ‘Product Code Name.3’ column to ‘Product
Color Code’
Remove Delimiter
‘-’
◦ Select ‘Product Category Code’ column
◦ Go to ➔ Transform → Text Column → Extract
→ Text After Delimiter
◦ Type delimiter in Count box as ‘-’
Remove Delimiter
‘-’
◦ Select ‘Product Color Code’ column
◦ Go to ➔ Transform → Text Column → Extract
→ Text After Delimiter
◦ Type delimiter in Count box as ‘-’
YOU GET WHAT YOU SEE IN FIGURE
Import File
‘Employee
Details.xlsx’
◦ Click on ‘Home’ → ‘New
Source’ → ‘Excel’
◦ From Data folder select
‘Employee Details.xlsx’
◦ Select ‘Employees’ table
◦ Click ‘Ok’
 This will split between uppercase & lowercase

◦ Select “Region/Branch’ column of ‘Employees’ table.

Split Column: By ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’ group →
Uppercase to Lowercase ‘Split Column’ → ‘Split by Uppercase to Lowercase’
◦ Click ‘Ok’
YOU GET SPLIT BETWEEN UPPERCASE AND LOWERCASE
REMOVE THE LAST STEP ‘Split Column By Character Transition’
FROM APPLIED STEPS
 This will split between lowercase & uppercase

◦ Select “Region/Branch’ column of ‘Employees’ table.

Split Column: By ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’ group →
Lowercase to Uppercase ‘Split Column’ → ‘Split by Lowercase to Uppercase’
◦ Click ‘Ok’
You get Two New ◦ Rename ‘Region/Branch.1’ column to ‘Region’

Columns ◦ Rename ‘Region/Branch.2’ column to ‘Branch’

Split Column: By Digit ◦ Select ‘Hiredate’ column in ‘Employees’ query table
◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’ group →
to Non-Digit ‘Split Column’ → ‘By Digit to Non-Digit’
YOU GET THREE COLUMNS ‘Hiredate.1’, ‘Hiredate.2’
AND ‘Hiredate.3’
 ◦ Rename column ‘Hiredate.1’ to ‘Month Day’
Rename Columns ◦ Rename column ‘Hiredate.2’ to ‘Hire Day’
◦ Rename column ‘Hiredate.3’ to ‘Hire Year’
 ◦ Select ‘Hire Month’ column in ‘Employees’ query table

Replace ‘/’ from ‘Hire ◦ Go to ➔ Transform → ‘Any Column’ group → ‘Replace

Values’
◦ Type: Value to Find : / character
Day’ ◦ Type : Replace with: Do no type any thing here
◦ Click ‘Ok’
THIS REMOVE ‘\’ CHARACTER FROM ‘Hire Day’ COLUMN
REPEAT STEP AND REMOVE ‘\’ FROM COLUMN ‘Hire Year’
 ◦ Select ‘Product Name’ column in ‘Inventory’ query
Split Column: By Non- table

Digit to Digit ◦ Go to ➔ ‘Transform’ ribbon → ‘Text Column’

group → ‘Split Column’ → ‘By Non-Digit to Digit’
 ◦ You get two new columns named ‘Product Name.1’
and ‘Product Name.2’ respectively.
Rename New Columns ◦ Rename ‘Product Name.1’ to ‘Brand Name’.
◦ Rename ‘Product Name.2’ to ‘Product Name’.
Merge Column: Text Transformation
◦ You can select multiple column of a query table and merge them in a
single column
◦ Use CTRL key to select individual column not in sequential order
◦ Use SHIFT key to select column situated in sequential order (side by
side)
 ◦ Select ‘Employees’ query table
◦ Select the ‘Hire Month’ column

Merge Columns ◦
◦
Hold down SHIFT and select the ‘Hire Year’ column
This select all three columns ‘Hire Month’, ‘Hire Day’ & ‘Hire
Year’ in sequence.
◦ On the Add Column tab, click Merge Columns
‘Hire Date’ COLUMN ADDED AT END
 ◦ Change data type of ‘Hire Date’ Column to ‘Date’

Change Data Type ◦ Go to ➔ Transform → ‘Any Column’ group →

‘Data Type’
◦ Select ‘Date’ from list
‘Hire Date’ APPEARS IN PROPER DATE FORMAT