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

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn how to import data from multiple Excel workbook files and combine them into a single proper data set using Power Query. Master techniques for transforming file extensions, filtering Excel files, extracting file names to create new columns, using custom functions, and appending worksheets. Apply correct data types, load data to Excel sheets, and create pivot tables. Discover how to refresh queries when adding new files or changing folder paths. Gain practical skills in data manipulation and analysis through hands-on examples and comprehensive explanations of Power Query features.

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.