Power Query Editor: Advance Topics
Welcome to the second part of our exploration of Power Query Editor in Power BI. In this part, we will dive into more advanced data transformation techniques. As you work with more complex datasets, you may often need to combine information from different tables or sources, pivot and unpivot data, or perform advanced aggregations. By learning these techniques, you’ll gain the ability to manipulate and analyze your data more effectively, making your reports and insights even more powerful.
We have to get a new data set to practice below Transformation.
Connecting a CSV file:
• Click the link below, an excel file will open in your browser, click File(in the top left),choose Download option then
Microsoft excel to download the formdate dataset to practice Power Query Editor. Formdate
1. In Power Query Editor click on New Source, select file type then connect. Open your file.
2. Choose Semicolon in delimiter to separate the data properly, and click Transform data.
Let’s continue the transformations with some advanced transformation tools in Power Query editor.
6. Splitting and Merging Columns
• Splitting Columns: You can split a column into two parts, like splitting a “Date1” into day, month, and year.
Suppose we want to know the Year of Acquisition.
1. Select the column, go to Transform > Split Column, and choose the option that works (e.g., by delimiter).
2. Choose Comma as the delimiter (i.e. columns split from “,”) and click OK.
3. You can rename the column as “Year of formation” by double clicking the column name.
- Merging Columns: Combine two columns into one, like merging “Country name” and “Country code.”
1. Select both columns by pressing and holding Ctrl key, and choose Merge Columns.
2. Select a Separator (e.g. Equals sign or space) and give a new column name, click OK.
7. Conditional Columns
You can create a new column with a value based on conditions, such as whether a year is before or after a certain date.
• Example: Add a column that assigns a value based on the Year of Formation (e.g., “Before 1960” if before 1960,
“After 1960” if after).
1. Go to Add Column > Conditional Column and set the conditions, click ok.
8. Creating Columns from Examples
Power BI allows you to create new columns based on patterns from existing data.
• Example: One column with country names (e.g., “Algeria”).
• Another column with the year the country was formed (e.g., “1962”).
• You want to create a new column that combines this information into a sentence, like “Algeria formed in 1962”.
1. Select Country and Year of Formation columns by pressing Ctrl key. Go to Add Column > Column from
Examples> From Selection
2. In the new column that appears, type a sentence using information from both columns. For example, you could type “Algeria formed in 1962”. Power query editor generates sentences from the example sentence you have given for all the rows. When you press Enter, Power Query will automatically generate similar sentences for all rows.
9. Merging Queries (Joining Tables)
Merging queries allows you to combine data from two tables based on a shared column.
• Example: Merge two tables—one with country data and another with GDP data—by joining them on the Country
column.
1. Go to Home > Merge Queries>Merge queries as New.
2. Select the tables to merge, then select the columns on which you want to join the tables i.e. country in both the columns and click OK.
3. Click the arrows on the right side on newly generated column and deselect the columns that are already exist in first table i.e. Country, code, Region etc. And also deselect the checkbox below to get the proper names of columns.
10. Pivoting and Unpivoting Columns
• Pivoting: Converts unique values in a column into new columns. For example, you can pivot GDP by year,
making each year a separate column.
• Unpivoting: Takes columns and turns them into rows, useful when you have many columns with similar data.
Download below datasets and load in Power BI to practice this topic.
• GDP-2000-2016
1. Select the columns, right-click, and choose Unpivot Other Columns.
11. Appending Queries
Appending queries combines data rows from two or more tables with similar structures into one table.
• Example: Append new GDP data to an existing dataset. Download below datasets and load in Power BI to
practice this topic.
• . Clean these datasets by using first row as header and remove empty columns.
Country_group1
Country_group2
Country_group3
1. Go to Home > Append Queries and select Append as new.
2. Select Two or more tables option, then choose dataset to append and click Add, click OK.
13. Entering Data Manually
You can manually enter small amounts of data directly in Power BI.
• Example: Enter a table of population data manually.
1. Go to Home > Enter Data.
2. Enter the Data in the column cells. Enter the Table name below and click OK.
14. Creating an Index Column
An Index Column in Power BI is a column that assigns a unique number to each row in your dataset. This helps you keep track of rows or use the numbers for calculations or referencing purposes. The index column is especially useful when your data does not have a unique identifier.
Example: Create an index starting from 1.
1. Go to Add Column > Index Column.
15. Performance Options (Enable/Disable)
Power Query lets you enable or disable certain performance settings to optimize data loading. Disabling load will remove the table from the report.
• Go to Home > Options, and explore the performance options to improve query speed and efficiency.
