Advanced Analytics with Microsoft Tools

Program Outline

Day 1: Starting with Power BI

• Downloading Power BI Desktop
• Power BI Interface
• Importing Data to Power BI
• Understanding the Power Query User Interface
• Transforming Data with Power Query
• Some Examples and overview of POWER BI

Day 2: Data Preparation

• Data Models, Facts and Dimensions Data
• Intro to Transforming Data with Power Query Continued
• M Scripts and Loading Data to Power BI
• Editing Queries and Refreshing Data
• Power Query Transformation
• Introduction to Data Preparation
• Using Append method to combine Excel Worksheets
• Using Workbook Property Method to combine Excel Worksheets
• Comparing Append & Workbook Property Consolidation Methods
• Consolidating Clean Excel Workbooks from a Folder
• Consolidating un-clean Excel Workbooks from a Folder

Day 3:  Adding and Formatting Charts

• Formatting Power BI Charts
• Adding a new chart to the canvass
• Sorting a Visual
• Filtering a page with Filters Pane and Using Slicers
• Controlling Visual Interactions on the Canvass

Day 4: Data Analysis Expressions (DAX)

• Introduction to DAX
• Using CALENDARAUTO() to generate a Calendar Table
• Using FORMAT() Function to add Month, Year and Quarter Columns
• Connecting the Calendar Table to the Fact Table
• Using the MONTH() Function to Create a Custom Sort Column
• Writing simple Aggregation Measure
• Aggregation Measures
• CALCULATE() function
• Creating a shell table to keep Measures
• Writing Time Intelligence Formulas - the PREVIOUSMONTH() Function
• Calculating Month on Month Variance
• Using the REMOVEFILTERS() function and Calculating Percentage share
• Using SAMEPERIODLASTYEAR() to calculate SPLY Values
• Automatic Measures with Quick Measures

Day 5: Visualizing Measures

• Customizing the Report Theme
• Creating a Report Page Template
• Visualizing Monthly Revenue vs SPLY on a Line Chart
• Creating a Forecast from a Line Chart
• Visualizing Monthly Variance with a Waterfall Chart
• Creating a Matrix Table report with Data Bars
• Creating a simple Donut Chart
• Visualizing Current & Previous Period with Clustered Bar Chart
• Visualizing Changes in ranking over time with Ribbon Chart