Power Query Editor is a data transformation and cleansing available in power BI.
Performing data transformations:
Split the columns into seperate columns based on delimiter
Unpivot columns:
Unpivoted Columns:
Replace Value:
String data cleaning:
Age calculation:
Adding conditional column:
By default, a conditional column doesn’t have a data type automatically defined
Count status:
Group By:
Merging queries:
Scenario to find the teachers handling for specific students and
number of subjects each teachers handle.
Common Joins to perform:
Inner join in the table brings in only matching rows from both the primary and secondary tables.
However, Left join keeps all the rows from the primary table and bring in any matching rows from the related table.
Note type of joins:
Ensure to select the same number of columns to match in the preview of the primary and secondary tables.
Column comparison is based on the order of selection in each table. Matching columns must be the same data type such as text or number.
Appending Queries:
Append tables vertically using “append queries” option.
Conditions to append:
Append queries
- same number columns,
- sequence of datatypes
Once the data has been transformed we can preview the changes and load the data into power BI.
After appending check the count of rows by indexing in power query.
Recent Comments