Cheat Sheet: Power Query Functions one must know
What is Power Query
Power Query is a data preparation and data transformation engine from Microsoft. It provides GUI for sourcing and transforming the data
Refer the link
How to open Power Query Editor in Power BI
Under Home Tab -> Transform Data -> Transform Data
Change in Data Type. Under Home Tab → Data Type.
Remove Empty Rows. This helps in removing the empty rows. Home → Remove Rows → Remove Blank Rows
Remove Duplicate rows. This helps in removing the duplicate rows. Home → Remove Rows → Remove Duplicate
Remove Errors. This helps in removing the error rows. Home → Remove Rows → Remove Errors
Change Data Source. This helps in changing connection details. Or use to clear permissions. Home → Data source settings
Use First Row as Header. It helps move the first row as a header. Home → User First Row as Headers → User First Row as Headers
Remove Columns. Home →Remove Columns → Remove Columns. Remove columns: remove selected columns. Remove Other Columns-> Remove the columns other than selected columns.
Merge Queries. It helps in combining two or more tables using various kinds of joins. It merges rows of both tables. Home → Merge Queries → Merge Queries/Merge Queries as New. Join options are inner, left, right, and full. You can merge in the selected table or in a new table.
Append Queries. It appends two tables. It is like a union all in SQL/Sets. Home → Append Queries -> Append Queries/ Append Queries as New. You can Append it in the selected table or in a new table.
Unpivot Data. It can move columns to rows. Options are using select columns or other columns. Transform → Unpivot Columns/ Unpivot Other Columns
Pivot Data. It can convert rows to columns. Options are using select columns or other columns. Transform → Pivot Columns
Split Column. Transform → Split Columns. Options are By Delimiter, By Number of Characters, By Positions, and others
Format Columns. Transform → Format. Options are lowercase, UPPERCASE, Trim, and Clean.
Extract Data. Transform → Extract. Options are Length, First Characters, Last Characters, Range, Text Before Delimiter, Text After Delimiter, and Text Between Delimiters
Fill Up/Down
Add Index Columns
Extract Date Part in New Column
Column Quality: Provide information about column quality. It provides percent of Valid, Error, and Empty.
Column Distribution: It shows you the distribution of values and provides a distinct and unique count. Unique are distinct that have only one occurrence.
Column Profile: It provides a complete column profile. Depending on the data type, one can get different statistics.
Please refer to my channel for more videos on these topics
Remove Empty and Remove duplicate Rows (Power Query)
How to Change Connection Details
Merge Tables (Power Query) :
Append Tables (Power Query)
Split Column (Power Query)
Split Column By Digit to Non-Digit & Non-Digit to Digit
Fill Up Fill Down (Power Query)
Add Index Column
These power query operations are useful for an interview and PL 300 examination preparation
Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.
You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share
