Classroom/ Online: Yes/ Yes
Scheduling Date(s):
Note: Please click specific date for detailed venue and course fee etc.
Automate Your Business Reports with Excel Power Query, Data Model and Power Pivot & DAX
Using the traditional way of reporting with Pivot Table is no longer going to fulfil the new needs of the organization. If you want to EFFORTLESSLY combine large amount of data, clean it using Excel frequently, you have to make use of the newly added Power Query and Power Pivot functions in Excel.
With Power Query, you can simply clean and organize your large excel worksheet once and it will be able to record how you did it and repeat exactly the same steps for future update. It is done without using Macro or VBA.
Pivot Table used to be the function we use to create our reports. But it is not perfect because it is not able to add calculations like growth and variance into report. Instead, these calculations have to be done outside the Pivot Table. With Power Pivot, this is no longer necessary because calculations can be done within Pivot Table and done FAST!
Sign up for this course and learn the new ways of data analysis, data visualization and reporting.
With Power Query, you can simply clean and organize your large excel worksheet once and it will be able to record how you did it and repeat exactly the same steps for future update. It is done without using Macro or VBA.
Pivot Table used to be the function we use to create our reports. But it is not perfect because it is not able to add calculations like growth and variance into report. Instead, these calculations have to be done outside the Pivot Table. With Power Pivot, this is no longer necessary because calculations can be done within Pivot Table and done FAST!
Sign up for this course and learn the new ways of data analysis, data visualization and reporting.
Objective
At the end of this course, you will be equipped with the skills to
Pre-requisite
Participants must have experience in using
- Automatically clean your raw data using Power Query
- Create your professional-looking reports and charts with Power Pivot Table
- Update your reports with just one click
Pre-requisite
Participants must have experience in using
- Simple worksheets functions
- Simple Pivot Table
- Works with multiple worksheets of data
Outline
1. Fundamentals of Pivot Table
o Create a Sales trend analysis report using Pivot Chart
o Create a customer risk assessment report using Pivot Table and conditional formatting.
2. Cleaning data in Power Query
o Import data
o Remove unwanted columns
o Remove headers from data source
o Remove empty rows from raw data
o Remove invalid rows of data
o Extract Text
o Split columns into two or more columns
o Combine two or more columns into one
o Capture your steps for automation
o Convert dates to acceptable format
o Re-arrange columns for easy reference
o Merge 2 data sources (worksheets or files) into one, like performing multiple VLOOKUPs.
o Collate multiple data sources like monthly data, from cost centres, business units, etc.
o M Language
3. Power Pivot
o Activate Power Pivot in your Office 365 / M365 Excel app
o Compare the numbers between 2 periods (Year, Quarter or Month)
o Organize the dates into Year, Quarter, Month on the fly
o Use DAX to create calculated columns like growth and variance between 2 periods
o Calculate YTD numbers in Power Pivot
4. Learn what is Data storytelling and how to become an effective data storyteller.
5. Identify the different levels of data analysis
o Create a Sales trend analysis report using Pivot Chart
o Create a customer risk assessment report using Pivot Table and conditional formatting.
2. Cleaning data in Power Query
o Import data
o Remove unwanted columns
o Remove headers from data source
o Remove empty rows from raw data
o Remove invalid rows of data
o Extract Text
o Split columns into two or more columns
o Combine two or more columns into one
o Capture your steps for automation
o Convert dates to acceptable format
o Re-arrange columns for easy reference
o Merge 2 data sources (worksheets or files) into one, like performing multiple VLOOKUPs.
o Collate multiple data sources like monthly data, from cost centres, business units, etc.
o M Language
3. Power Pivot
o Activate Power Pivot in your Office 365 / M365 Excel app
o Compare the numbers between 2 periods (Year, Quarter or Month)
o Organize the dates into Year, Quarter, Month on the fly
o Use DAX to create calculated columns like growth and variance between 2 periods
o Calculate YTD numbers in Power Pivot
4. Learn what is Data storytelling and how to become an effective data storyteller.
5. Identify the different levels of data analysis
Who should attend
The training is ideal for those (such as Directors, Head’s, Leaders, Managers and executives across all industries and all professionals) who know how to create a Pivot Table report.
Mode of Training:
Face to Face workshop
Course Fees:
Non-WSQ funded (no assessment): $807.50 per pax when you register 1 or 2, $788.50 per pax when you register 3 & above. please register directly online and keep ticking the membership option, membership is free.
WSQ Funded (with assessment): $1000 (Secret Hacks of Excel Data Analytics for PMET) , please register first, and later transfer your details to WSQ funded course.
Mode of Training:
Face to Face workshop
Course Fees:
Non-WSQ funded (no assessment): $807.50 per pax when you register 1 or 2, $788.50 per pax when you register 3 & above. please register directly online and keep ticking the membership option, membership is free.
WSQ Funded (with assessment): $1000 (Secret Hacks of Excel Data Analytics for PMET) , please register first, and later transfer your details to WSQ funded course.
Methodology
Two days of hands-on workshop in Hotel
Particular of Jason Khoo
Jason Khoo is a Microsoft Certified Trainer (MCT), ACTA certified trainer of 20 years. He is a Microsoft Certified Power BI Data Analyst Associate and a data analytics practitioner of 30 years. During his time of employment, he has worked in Tibs (currently SMRT), MobileOne Ltd, 3M and Virgin Mobile.
During his tenure, he has worked with multiple databases, extracted and downloaded information from systems and run numerous analysis. These analyses spanned across many departments such as Finance, Sales, Marketing, Human Resources, Payroll, etc.
After his employment, he worked as a data analytics consultant for many companies, including Discovery Asia, 3M, Tanah Merah Country Club, Johnson and Johnson Vision Care, National Environmental Agency, Timberland, etc.
His data analytics skills combined with Excel allowed him to deliver many reports to the clients without them having to incur software cost. These reports include Dashboards, Top 10 charts, Risk Analysis, Business Models, KPIs, etc.
As a trainer, he is passionate about transferring his knowledge and imparting his skills to his participants. To day, he has trained thousands in his face to face workshops and had conducted talks for thousands as well.
Trans-Island Bus Services Ltd (Tibs) –Operations and Revenue Analytics, Passengers Travelling Behaviour Analytics
• Daily tracking of revenue,
• Prepare KPI for LTA,
• Fare revision analysis
Mobile One Asia Ltd (M1) – Financial analytics, Customer Call Usage Analytics
• Full company budgeting and business planning,
• Monthly Analysis,
• KPI reporting
3M – Sales, Cost and Inventory analytics
• Daily and Monthly Sales Forecasting,
• Analysis of Marketing Budget,
• Analysis of Inventory and shipments.
• Cost Analysis for new products (BOM analysis)
Virgin Mobile – Retail, Marketing, Payroll, Admin, IT, Finance Analytics
• Daily KPI reporting,
• Monthly Analysis for Finance, Payroll,
• Developed Fully Automated Business Model driven by KPI
