Excel Business Analytics Free Course - Forecasting, Linear Programming, Simulation & more. - Basic Excel Business Analytics #32: Power Query Import Multiple Excel Files with Multiple Sheets

Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm Learn how to import multiple Excel workbooks (each with the store name in the file name) with multiple sheets in each workbook (each sheet contains the Sales Rep name) and import the sales data into a proper data set, including a column for the sales rep name (data from the sheet tab names) and a column for the store name (data from file name): 1) (00:04) Download File Information 2) (00:23) Look at Excel Workbooks that need to be imported, including the names of each Sales Rep on each sheet tab. 3) (01:20) Power Query, From File, From Folder, to import files from a folder 4) (02:07) Remove Other Columns, being sure to keep file with File name, which contains the store name. 5) (02:24) Add Column and use Power Query Function called Excel.Workbook, so that we can extract the data from the Excel workbook. 6) (03:13) Use Replace Values feature in Power Query to extract the store name from the file name. 7) (03:47) Remove Content Column 8) (03:54) Expand data Column which will expose the Data Column (Data in Excel Workbook), Item Column (contains sheet name data), Kind Column (contains object information such as: Sheet, Table and Defined Names). 9) (04:12) Filter the Kind Column to remove Tables and Defined Names and keep Sheets only. 10) (04:39) Filter Item column to remove sheets that were not properly named (have default names such as Sheet1, Sheet2). We use the “Does Not Contain” Filter to keep sheets that do not contain the text “sheet”; another way to think about it is: “Filter out sheet tabs that have the text ‘sheet’ in them” 11) (05:15) Amazing results: data from sheet tabs and file names is retained for proper data set. 12) (05:27) Remove columns that are not File Name (Store name), Item (Sales Rep Name) and Data. 13) (05:37) Expand data from Excel Workbooks. 14) (05:42) “Use First Rows As Headers” to promote the Field Names from the first sheet in the first workbook to Field Names. 15) (05:53) Filter out Field names from other Sheet tabs. 16) (06:48) Rename Columns 17) (07:15) Add correct Data Types before importing 18) (07:37) Close and Load To a Table on Existing Sheet 19) (08:11)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.