Automate Data Tasks in Excel using Power Query

The objective of this Training:

The objective of this Training is to help the audiences develop their skills and knowledge on Power Query and Large Data Management on the purpose that they can prepare their data more powerfully through transformation and tell the story by visualizing through automation.

Key Takeaways from This Training:

• Automation of Tedious Task.
• Large Data Set Management.
• Data Visualization for Storytelling.
• Apply Learning in the Workplace

Detailed Training Outline:

Section 1: Introduction – Power Query :

a. What is Power Query
b. What Does Power Query Do

Section 2: Getting Data into Power Query :

a. Getting Data From Excel Workbook
b. Getting Data From Excel Table
c. Getting Data From Folder
d. Getting Data From Text/CSV
e. Getting Data From Website
f. Getting Data From PDF (Office 365)

Section 3: Power Query Editor :

a. Load and Load to
b. Transform Data
c. Introduction – Power Query Editor
d. Introduction – Tab of Power Query Editor
e. Difference Between Transform and Add Column

Section 4: Text Column :

a. Format Text
b. Split Column
c. Extract Text
d. Merge Column

Section 5: Number Column

a. Statistics: Count Value and Count Distinct Value
b. Standard: Percentage and Percentage of
c. Absolute Value
d. Rounding
e. Is Even and Is Odd

Section 6: Date and Time

a. Basic Date Function
b. Day of Year, Quarter, Month, and Week
c. Find Age or Length of Service
d. Day Name
e. Day Difference

Section 7: Combine

a. Merge Queries
b. Append Queries
c. Difference Queries vs Queries as New

Section 8: Data Reshaping

a. Pivot Column vs Unpivot Column
b. Transpose
c. Reverse Rows

Section 9: Add Column

a. Column From Examples
b. Custom Column
c. Conditional Column
d. Index Column
e. Duplicate Column

Section 10: Groupin

a. Group By Basic
b. Group By Advance
c. Group Operations: Count and Sum
d. Add Grouping and Aggregation

Section 11: Power Query: Others

a. Advanced Editor
b. Reduced Rows
c. Data Format
d. Replace Values and Errors
e. Difference between: Use First Row as Header & Use Headers
as First Row
f. Fill
g. Query Edit Option
h. Refresh New Data

Projects:

a. Dynamic Top N
b. Reconciliation Data
c. Data Reshaping: Pivot and Unpivot Column (3 Cases)
d. Folder Connections for Adding New Data from New Excel Workbook.

Note:

• After completing every lesson click on the “COMPLETE” button the go to the next lesson.
• Must set up your first name, last name, and display name from settings of your profile for your certificate.
• don’t click on the “FINISH COURSE” button.
• If you click on the “FINISH COURSE” button then the course will be finished and a Certificate will be generated.
• More details: https://thrivingskill.com/faqs/