Power Query & Power Pivots

This course helps create fast, repeatable data transformations that greatly improve Excel analysis and reporting capabilities. This will cover: Import Excel file, remove top rows, use first row as header, remove columns, change type, split columns by delimiter, rename columns, close & load, merge columns, conditional columns, reorder columns, rename steps, move steps, view 'M' code, sort rows, filter rows, rename query, reference, group-by, duplicate, refresh, merge.

This course will help you learn to control your data - - instead of letting it control you. Invest an hour and get a lifetime of benefits.

Power Query or Power BI editor has many features. This course does not cover everything but provides a solid foundation so you can navigate the ribbons and learn more on your own.


Topics Covered

Import Data from Various Sources: Excel, Text, Web
Aggregate or summarize data
Append Query: Combine one below other
Combine Different files of similar format from a folder
Combine different sheets of similar format from a file
Creating calculating columns / fields which never existed in the source data.
Deleting / sorting rows, columns or blanks
Extract
Filtering out data you don't need
Find & replace text
Incorporating the next month (or period) data to an existing report
Merge Query: Combine side by side
Merging / consolidating / appending data from Text files, CSV files or database tables.
Perform same steps again when receive new file or data in next month
Pivot data
Unpivot Data for Pivot Tables

Functions Covered

Aggregation
SUM, AVERAGE
MIN, MAX
SUMX (and other X functions)

Counting
COUNT, COUNTA
COUNTBLANK, COUNTROWS
DISTINCTCOUNT

Logical
AND, OR
NOT
IF, IFERROR

Additional Functions
CALCULATE
DIVIDE
COLUMN
RELATED
TODAY
DATEDIF
SWITCH
HASONEVALUE
Removing spaces and special characters from your data.
Split Columns
Structured column
Transforming text, numeric, and date columns.
Transpose Data