Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

Power Query - Import Multiple Excel Files and Combine into Proper Data Set

ExcelIsFun via YouTube

Start learning Write review

Details

Start learning

Provider

YouTube
Pricing

Free Video
Languages

English
Effort

32 minutes
Sessions

Self-Paced
Level

Beginner

Found in

This course demonstrates how to use Power Query to import multiple Excel workbooks from a folder, extract city and sales representative information from file and worksheet names, filter and append valid data, apply data types, and load a refreshable dataset into Excel. It also builds a PivotTable report and updates the query when new files or folder paths change.

Syllabus

) Introduction.
) Look at Data Import Files and the different objects that are in an Excel File.
) Import Excel Files From Folder.
) Look at Excel File in Power Query Editor.
) Transform extensions to all lowercase.
) Filter to include only Excel Files in import process.
) Extract Excel File Name to create New Column for City. Split By Delimiter..
) Power Query Options: Don’t Change Data Type.
) Rename Column and Remove unwanted columns.
) Add Custom Column with Excel.Workbook Function (M Code Function). Explanation of what functions extracts from the Excel Files..
) Filter Out Excel Objects that do not meet Criteria = Sheet.
) Filter out names that Do Not Begin With Sheet. Extract Worksheet Name to create New Column for SalesRep..
) Final Append to get all Excel Worksheet that contain Proper Data Sets with a proper SalesRep Name..
) Apply correct Data Types.
) Load to Excel Sheet.
) Change Default PivotTable Layout & Options.
) Build PivotTable Report.
) Definition of a PivotTable.
) Add New Excel Workbook Files to the Folder & Refresh the Query and PivotTable.
) Edit Query when Folder Path Changes.
) Summary.