Import Multiple Excel Sheets From Multiple Excel Workbooks
See how to use Power Query to easily import multiple sheets of data from multiple Excel Workbooks and easily have Power Query Add a new column that is populated with the Sales Rep Name from the Sheet Tab:
(00:10) Problem Setup
(01:32) Import From Folder
(01:55) Remove Other Columns
(02:05) Add Custom Column with =Excel.Workbook([Content]) to get Excel Workbook Content
(02:38) Remove Column
(03:02) Filter by “.Kind” to Filter out Tables and Defined Names
(03:58) Use Filter Feature to Filter out Sheet Tab Names that do not contain a proper Sales Rep Name.
(05:20) Add Custom Column to Promote Headers with =Table.PromoteHeaders(“*.Data”)
(06:16) Expand Tables to create on large table.
(06:54) Add Data Types.
(07:33) Save and Load to Table in Excel Worksheet.
(07:57) Set Query Property to Update When File Is Opened.
(08:27) Add New Sheets to Source Files and see that Power Query Updates Automatically.