Power Query is a half-day seminar designed to show participants how to mine data that could be housed in different source formats. The course teaches participants to import the data, clean the data, create functions with the data, and finally to turn the data into easy-to-analyze visual formats.
See specific content information below in sections "Expected Outcomes" and "Attend and You Will Learn."
Why You Should Attend
This course will teach participants how to combine "Big Data" from many different types of sources easily and efficiently.
Who Should Attend
Any Excel user eager to learn how to retrieve and combine data from multiple and different sources should attend. In this day and age of the "Internet of Things," knowing how to find, combine and manipulate data can make a huge difference for decision makers.
Attend and You Will Receive
Participants who successfully complete this course will receive a certificate of completion from NC State University.
Users attending this course should have completed Excel Levels 1 and 2 or have equivalent experience.
Attend and You Will Learn / Expected Outcome
The participant should use the learned skills as soon as possible upon their return to work. With continued use and practice, the participant will be able to efficiently and effectively retrieve data from many sources, combine the data, and manipulate the data.
Attend And You Will Learn
- What is Power Query
- Installing Power Query
- The Power Query Interface
- From Web
- From Excel File
- From Current Workbook
- From CSV or Text File
- From Folder
- From Database (SQL)
Combining Multiple Sources
- Multiple Excel Tables or Named Ranges
- Worksheets not Correctly Formatted
- Appending Queries
- Merging Queries
Transforming and Cleaning Data
- Editing Queries
- Data Types
- Fixing Dates with the Locale Setting
- Columns - Move, Remove, Rename & Duplicate
- Splitting Columns
- Merging Columns
- Filtering Rows
- Remove Duplicates & Errors
- Replace & Fill
- Transforming Text & Numbers
- Transforming Dates
- Adding Custom & Index Columns
- Adding Custom Calculated Columns
- Adding Custom Date & Time Columns
- Duplicating & Referencing Another Query
Data Destinations & Refreshing Queries
- Loading to the Workbook Options
- Loading to the Data Model or Power Pivot
- Load Settings & Automatic Refresh
- Load Directly into Power Pivot in Excel