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 CoveredImport Data from Various Sources: Excel, Text, WebAggregate or summarize dataAppend Query: Combine one below otherCombine Different files of similar format from a folderCombine different sheets of similar format from a fileCreating calculating columns / fields which never existed in the
source data.Deleting / sorting rows, columns or blanksExtractFiltering out data you don't needFind & replace textIncorporating the next month (or period) data to an existing
reportMerge Query: Combine side by sideMerging / consolidating / appending data from Text files, CSV
files or database tables.Perform same steps again when receive new file or data in next
monthPivot dataUnpivot Data for Pivot TablesFunctions CoveredAggregationSUM, AVERAGEMIN, MAXSUMX (and other X functions)CountingCOUNT, COUNTACOUNTBLANK, COUNTROWSDISTINCTCOUNTLogicalAND, ORNOTIF, IFERRORAdditional FunctionsCALCULATEDIVIDECOLUMNRELATEDTODAYDATEDIFSWITCHHASONEVALUERemoving spaces and special characters from your data.Split ColumnsStructured columnTransforming text, numeric, and date columns.Transpose Data