Summary
Keywords
Full Transcript
Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm Learn about how to use Power Query to Import Multiple Excel Files with “Bad Data” (Data that is not in a Proper Data Set) into a single Proper Data Set and Create an amazing PivotTable that shows percentage Employee Response by Department To Company Survey: 1) (00:04) Download File Information 2) (00:27) Look at the multiple Excel files that contain Employee Survey Data that are not contained in a proper data set (Bad Data). 3) (01:50) Import many files with poorly set up data: Power Query Get External Data group, From File, From Folder 4) (02:54) Add Column with Excel.Workbook Power Query Function to get the data from the Excel files. 5) (03:43) Get Sheet Names from each sheet for a new column 6) (04:08) Filter feature in Power Query: Clean “Bad Data”. We filter out the survey question, empty cells (null), Filed names (Employee/Answer), and Count summary row. 7) (05:30) Unpivot Columns feature: Gather all the “Yes” Answers and “No” Answers into a single column 8) (05:50) Rename columns 9) (06:20) Close and Load to Table on sheet 10) (06:41) PivotTable for company overall survey results as a % of Column Total. 11) (07:59) Original Survey Question 12) (08:43) PivotTable to break out survey results by Department. See the Show Values as: % of Column Parent Total. 13) (09:36) Drop new files into folder and see that the entire set of reports, including the Power Query that cleaned and transformed the data, all update perfectly!!! 14) (10:35) 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.
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
