Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm
Learn about how to clean and transform data to prepare it for data analysis using Formulas, Flash Fill, Power Query and Text To Columns:
1) (00:19) Intro to Import, Clean and Transform Data for this section of the class
2) (02:00) Use VLOOKUP to create better labels for our data set and for our PivotTable Report with % of Column Totals and a Slicer (Filter)
3) (05:40) Get rid of extra spaces with the TRIM Function
4) (07:11) Get rid of extra spaces with Flash Fill
5) (09:08) Formula: Convert ISO Dates like 20140212 (Year, Month, Day) to Serial Number Dates. TEXT function, Custom Number Format “0000-00-00” and add zero (any math operation) to convert number stroed as text back to a number.
6) (11:14) Text To Column: Convert ISO Dates like 20140212 (Year, Month, Day) to Serial Number Dates.
7) (12:12) Power Query: Convert ISO Dates like 20140212 (Year, Month, Day) to Serial Number Dates.
8) (15:16) Formula: Split Region and City from Same Cell. LEFT and SEARCH functions.
9) (17:19) Flash Fill: Split Region and City from Same Cell.
10) (17:44) Text To Columns: Split Region and City from Same Cell.
11) (18:43) Formulas: Get Date and Sales from a transaction description in a single cell. See the MID, SUBSTITUTE, SEARCH and REPLACE functions.
12) (23:53) Power Query: Get Department, Product, Date and Sales from a transaction description in a single cell.
13) (27:35) Compare the dynamic (ability to update when source data changes) aspects of Formulas and Power Query.
14) (28:27) Summary and Conclusion
Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.