Big Data: Ms Excel 2013/2016 Power Query and Power Pivot - TED Learning

Big Data is a term that has been widely used in the modern days to describe large and complex data sets that require analysis to ease decision making and optimize business performance. As big data in company is growing, the challenge of IT personnel, Business Intelligence, Business Analyst and Data Analysts is to analyze big data which is so complex and convert to meaningful information for better decision making. This two days training program helps you to gain insights out of the Big Data using Power Query and PowerPivot 2010/2013.

Power Query is an ideal data transformation and manipulation tool for Microsoft Excel. If you frequently use Excel to transform, enrich, append or merge data in your daily work, then this tool is right for you.

PowerPivot is an add-on for Microsoft Excel 2010 and 2013. It enables you to import your big data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, calculated columns and measures using formulas, build Pivot Tables and Pivot Charts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance

During the training, you will use Power Query to

1. Extract data from different sources and databases
2. Automate the data transformation and data cleansing processes
3. Integrate data from multiple sources into a single source
4. Merging data without the need for VLOOKUP, INDEX & MATCH.
5. Perform calculation to aggregate data.

Besides that you also use PowerPivot to:

1. Create relationships between multiple tables.
2. Perform calculations in PowerPivot.
3. Create Pivot tables and Pivot charts
4. Discover to Data Analysis Expression Language

Delegates should be proficient users of Excel and be experienced in creating Excel Pivot Tables and Pivot Charts.

USING POWER QUERY

1. Introduction to Power Query
2. Appending data
3. Merging Table
4. Transforming Data
5. Power Query Formulas
6. Cleaning Dirty Data
7. Databases as sources
8. Exchange as a data Source

USING POWERPIVOT

1. Import external data from multiple sources.
   1. Client database
   2. Excel tables
   3. CSV files
2. Handling massive data from multiple sheets.
3. Create relationships between multiple tables.
4. How to create a user friendly models
5. Perform calculations in PowerPivot
   1. Calculated columns.
   2. Measures (Calculated fields)
6. Create Pivot Tables and Pivot Charts.
7. Introduction to Data Analysis Expression language (DAX)