Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks

Опубликовано: 29 Ноябрь 2014
на канале: excelisfun

127,500

627

Download Files: http://people.highline.edu/mgirvin/ex...

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:
1. (00:10 Minute Mark) Problem Setup
2. (01:32 Minute Mark) Import From Folder
3. (01:55 Minute Mark) Remove Other Columns
4. (02:05 Minute Mark) Add Custom Column with =Excel.Workbook([Content]) to get Excel Workbook Content
5. (02:38 Minute Mark) Remove Column
6. (03:02 Minute Mark) Filter by “.Kind” to Filter out Tables and Defined Names
7. (03:58 Minute Mark) Use Filter Feature to Filter out Sheet Tab Names that do not contain a proper Sales Rep Name.
8. (05:20 Minute Mark) Add Custom Column to Promote Headers with =Table.PromoteHeaders(“*.Data”)
9. (06:16 Minute Mark) Expand Tables to create on large table.
10. (06:54 Minute Mark) Add Data Types.
11. (07:33 Minute Mark) Save and Load to Table in Excel Worksheet.
12. (07:57 Minute Mark) Set Query Property to Update When File Is Opened.
13. (08:27 Minute Mark) Add New Sheets to Source Files and see that Power Query Updates Automatically.

Extract Sales Rep Name from Sheet Tabs. Include Sheet Tab Names as New Column. Promote Headers. Table Promote Headers. Filter Does Not Contain. Set Query Property. Update When File Is Opened. Remove Other Columns.

Miguel Escobar and Ken Puls are doing online workshops on Power Query www.powerquery.training . This training is not by excelisfun.