1. The Foundations & Concept of Data in Excel - our “3-2-1” Framework of Data

• Three Types of Data
• Dates
• The challenge of “looks like date but does not behave like date”
• What is a DATE?
• Recommended Date Formatting – dd-mmm-yyyy
• Extracting Month Name, Year, Day Name, Week Number, etc. from a date.
• Text
• The main challenges
• Cases
• Spaces
• Numbers
• The ideal and recommended formatting – Accounting
• Why Accounting is our recommendation
• Formatting large numbers as thousands ( 1,234,567 as 1,234 k)
• Formatting large numbers as millions with a decimal ( 1,234,567 as 1.2 M)
• Two Sources of Data
•  Raw Data
•  Calculated Data
• One Cell type – Range or Table

2. Data in “Excel Range” or in “Excel Tables” : The 1978 way of “Range” vs. 2020 way of “Table”

• Why copy formulas in the column after you have typed one?
• Why expand “range” after addition of new row or column to data.
• Use of Excel Tables – end of Excel “range” way.
• Understand the benefits of using an Excel table over a range of data
• Convert a data range into an Excel table
• Review of the Excel table contextual tab
• Quickly format a table with pre-defined table styles
• Filter data within a table
• Quickly add columns and rows into an existing table
• Add a Total row to a table for quick calculations
• Automatically add a function to all rows within a table
• Learn the benefits of using an Excel table to create a PivotTable
• Why name a Table with “tbl”?

3. Slicers for Data – avoid old FILTERS

Old Way of FILTERs vs NEW Way of SLICERS

• Concept of Horizontal Slicers
•  Best practices for Slicer position and size
•  Use of multiple columns
• Formatting Slicers using Styles
• Setting Default Slicer Style
• Deleting Slicers
• Glue your slicers so that even Excel can not move them
• Slicer Settings – best practices
• Slicers: One Pivot - Multiple Reports
• Super glue your slicers so that even Excel can not move them
• Positioning of Slicer with Pivot Tables
• To Print or Not to Print Slicers.
• Unlocking Slicers while Protecting a Sheet

4. Calculations Concept

• Relative and Absolute Reference
• Use of F4 Key
• Entering Formulas
• Using The Mouse to Enter Cell
• References in Formulas
• Editing Formulas – Using F2 Key
• The Order of Evaluation: BODMAS
• Using Parentheses to Change the Order
• Entering Functions
• The Structure of Functions
• The =SUM() Function
• Using the Mouse to Enter Range
• References
• Entering Functions in the Formula Bar
• Inserting Functions
• Applying AutoSum

5. Formatting Numbers, Text, Reports

• Number Formatting
• Dates Formatting
• Text Formatting
• Row and Column Formatting
• Additional Formatting Options: Styles
• Other Advanced Formatting
• Merging Cells
• Wrapping Text
• Transposing Data
• Using Paste Special Operations

6. Printing

• Preparing to Print
• Using the Spelling Checker
• Using Page Break Preview
• Page Setup Options
• Page Orientation
• Page Size
• Scaling
• Margins
• Custom Headers and Footers
• Print Titles: Rows or Column to Repeat
• Printing Worksheets

7. Power of IF and VLOOKUP – a Teaser

• =IF() Function
• =VLOOKUP() Function

8. Introduction to Pivot Tables

• Reports with ZERO Errors & ZERO Formulas
• Using Styles to Format Pivot Reports

9. Introduction to Charts

• Creating Charts with One Key Press!
• The FOUR Critical Steps
• Pie and Column Charts

1. The Foundations & Concept of Data in Excel - our “3-2-1” Framework of Data

Three Types of Data

• Dates
• The challenge of “looks like date but does not behave like date”
• What is a DATE?
• Recommended Date Formatting – dd-mmm-yyyy
• Extracting Month Name, Year, Day Name, Week Number, etc from a date.
• Text
• The main challenges
• Cases
• Spaces
• Numbers
• The ideal and recommended formatting – Accounting
• Why Accounting is our recommendation
• Formatting large numbers as thousands ( 1,234,567 as 1,234 k)
• Formatting large numbers as millions with a decimal ( 1,234,567 as 1.2 M)
• Two Sources of Data
•  Raw Data
•  Calculated Data
• One Cell type – Range or Table

2. Data in “Excel Range” or in “Excel Tables” : The 1978 way of “Range” vs 2020 way of “Table”

• Why copy formulas in the column after you have typed one?
• Why expand “range” after addition of new row or column to data.
• Use of Excel Tables – end of Excel “range” way.
• Understand the benefits of using an Excel table over a range of data
• Convert a data range into an Excel table
• Review of the Excel table contextual tab
• Quickly format a table with pre-defined table styles
• Filter data within a table
• Quickly add columns and rows into an existing table
• Add a Total row to a table for quick calculations
• Automatically add a function to all rows within a table
• Learn the benefits of using an Excel table to create a PivotTable
• Why name a Table with “tbl”?

3. Slicers for Data – avoid old FILTERS

Old Way of FILTERs vs NEW Way of SLICERS

• Concept of Horizontal Slicers
•  Best practices for Slicer position and size
•  Use of multiple columns
• Formatting Slicers using Styles
• Setting Default Slicer Style
• Deleting Slicers
• Glue your slicers so that even Excel can not move them
• Slicer Settings – best practices
• Slicers: One Pivot - Multiple Reports
• Super glue your slicers so that even Excel can not move them
• Positioning of Slicer with Pivot Tables
• To Print or Not to Print Slicers.
• Unlocking Slicers while Protecting a Sheet

4. Calculations Concept

• Relative and Absolute Reference
• Use of F4 Key
• Entering Formulas
• Using The Mouse to Enter Cell
• References in Formulas
• Editing Formulas – Using F2 Key
• The Order of Evaluation: BODMAS
• Using Parentheses to Change the Order
• Entering Functions
• The Structure of Functions
• The =SUM() Function
• Using the Mouse to Enter Range
• References
• Entering Functions in the Formula Bar
• Inserting Functions
• Applying AutoSum

5. Deep Dive – PivotTables – The Real Power of Data Analytics

• The NEW way of Pivot Tables: Create a Pivot Table that works for LIFE –on refresh, all new rows and columns of data should get included.
• The commonly faced challenges in a Pivot, and solutions for them.
• Challenge #1: There are lot of empty cells in the report – how to have “zero” value in all of them.
• The FIVE Critical Pivot Table Options
• Format Numbers (Not Cells)
• Report format: Style and Design Components to Format a Report
• The Report: Sum, Count
• The Report: Concept Of Data Cubes
• The Problem Of Pivot Report not taking New Data
• Analysis Escalator
• Historical or Descreptive Analysis
• Using Slicers instaed of Filetrs in Pivot Tables – Transition from 1978 to 2020 Way
• The 4 Quadrants of Data Analysis
• Understanding Dimensions 
• Time Dimension
• Product or Service Dimension
• Geography or Market  or Region Dimension 
• Customer or Prospect Dimension  
• Diagnostic Analysis 
• Show Amount as % Of Total
• % of Grand Total or % of Column Total – which is better?
• % Parent Row Totals
• % Parent Column Totals
• % Row Totals
• Variance Analysis – Difference in Value v/s Difference in %
• Running Totals in Value, %
• Ranking Largest to Smallest
• Index

6. The daily grind of data warriors - VLookup Functions 

• =VLOOKUP()
• How to lookup Vertically for data – data in other columns. 
• Eliminate the fixed range challenge created by “$A$1:$M$500” range way of VLOOKUP. Use TABLES
• Using VLOOKUP to find exact matches
• How to provide insurance against current and future “#N/A” errors
• Root Cause Analysis of why we get “#N/A” error and solutions to the root cause
• #N/A Reason: Unwanted Blank Spaces (bsp)
• #N/A Reason: Spaces that are NOT spaces in reality (Non Blank Spaces – nbsp)
• #N/A Reason: Transactional Data is text where as lookedup data is numbers.
• #N/A Reason: Transactional Data is number where as lookedup data is text
• #N/A Reason: Spelling Mistakes
• Looking across two different files without “$A$1:$M$500” range way. 
• Using VLOOKUP to Group number oriented data like Revenue, Stock, Accounts Receivables, Employee Tenure, etc using APPROXIMATE match component of VLOOKUP.
• Using MATCH and INDEX functions

7. Logical Functions

• =IF()
• =OR()
• =AND()
• =IF(IF(IF)) – Nested IF

8. Date Functions

• Is it a Date?
• Calculate the exact service length in YEARS, MONTHS, DAYS without dividing by 365! Use function DATEDIF().
• Find the number of days between two dates using =NETWORKDAYS.INTL()
• Calander days
• Working Days (without FRI-SAT as weekends)
• Working Days (without Public Holidays and FRI-SAT as weekends)

9. Conditional Formatting – Basics to Advanced

Bars

• Highlight Cell Rule
• Top-Bottom Rule
• Manual Rules
• Formula Based CF Rules 

10. Charts Basics to Creative Charts 

• Creating Charts with One Key Press!
• The FOUR Critical Steps
• Pie and Column Charts
• Creative Charts

11. Creating Interactive Dashboards Screenshots

• Dashboard planning
• Use of Filters inside Pivots.
• Using Pivot Charts
• Why keep formatting all Charts manually – let Excel do this work!
• Using Slicers
• Multiple Charts being controlled by several Slicers – the inter-activity
• Protecting your Dashboard

1. Advanced Functions

• =MATCH()
• =INDEX()
• =VLOOKUP() with nth Instance
• =SUMIF()
• =COUNTIF()
• =SUMIFS()
• =COUNTIFS()
• =RANK()
• =LARGE()
• =SMALL()
• =MAX(), MIN() with Array formuals
• =Array Formuals , the CSE Formulas
• =FORECAST()
• =User Defined Functions with a Macro
• =XLOOKUP()

2. Power Pivots – the Future Way – Alternative of VLOOKUP

• For Scalability and Usability
• Relationships
• STAR Schema – Data & Lookup Tables
• Data and diagram view
• Set up of Power Pivot
• Creating your first Power Pivot report
• Creating Reports Using PowerPivots

3. Power Query

• Import Data from Various Sources: Excel, Text, Web 
• Aggregate or summarize data 
• Append Query: Combine one below other 
• Combine Different files of similar format from a folder 
• Combine different sheets of similar format from a file 
• Creating calculating columns / fields which never existed in the source data. 
• Deleting / sorting rows, columns or blanks 
• Extract 
• Filtering out data you don't need 
• Find & replace text 
• Incorporating the next month (or period) data to an existing report 
• Merge Query: Combine side by side 
• Merging / consolidating / appending data from Text files, CSV files or database tables. 
• Perform same steps again when receive new file or data in next month 
• Pivot data 
• Unpivot Data for Pivot Tables 
• Removing spaces and special characters from your data. 
• Split Columns 
• Structured column 
• Transforming text, numeric, and date columns. 
• Transpose Data 

Excel Advanced Customized Deep Dive Data Analysis with AI Tools

(Contents of this course have been created based on PRACTICAL requirements and INDUSTRY standards) 

Duration: 2 Days (14 hours)

1. The Foundations & Concept of Data in Excel - our “3-2-1” Framework of Data

Three Types of Data

Dates

The challenge of “looks like date but does not behave like date”

What is a DATE?

Recommended Date Formatting – dd-mmm-yyyy

Extracting Month Name, Year, Day Name, Week Number, etc from a date.

Text

The main challenges

Cases

Spaces

Numbers

The ideal and recommended formatting – Accounting

Why Accounting is our recommendation

Formatting large numbers as thousands ( 1,234,567 as 1,234 k)

Formatting large numbers as millions with a decimal ( 1,234,567 as 1.2 M)

Two Sources of Data

 Raw Data

 Calculated Data

One Cell type – Range or Table

2. Data in “Excel Range” or in “Excel Tables” : The 1978 way of “Range” vs 2020 way of “Table”

Why copy formulas in the column after you have typed one?

Why expand “range” after addition of new row or column to data.

Use of Excel Tables – end of Excel “range” way.

Understand the benefits of using an Excel table over a range of data

Convert a data range into an Excel table

Review of the Excel table contextual tab

Quickly format a table with pre-defined table styles

Filter data within a table

Quickly add columns and rows into an existing table

Add a Total row to a table for quick calculations

Automatically add a function to all rows within a table

Learn the benefits of using an Excel table to create a PivotTable

Why name a Table with “tbl”?

3. Slicers for Data – avoid old FILTERS

Old Way of FILTERs vs NEW Way of SLICERS

Concept of Horizontal Slicers

 Best practices for Slicer position and size

 Use of multiple columns

Formatting Slicers using Styles

Setting Default Slicer Style

Deleting Slicers

Glue your slicers so that even Excel can not move them

Slicer Settings – best practices

Slicers: One Pivot - Multiple Reports

Super glue your slicers so that even Excel can not move them

Positioning of Slicer with Pivot Tables

To Print or Not to Print Slicers.

Unlocking Slicers while Protecting a Sheet

4. Deep Dive Data Analysis – PivotTables – The Real Power of Data Analytics

The NEW way of Pivot Tables: Create a Pivot Table that works for LIFE –on refresh, all new rows and columns of data should get included.

The commonly faced challenges in a Pivot, and solutions for them.

Challenge #1: There are lot of empty cells in the report – how to have “zero” value in all of them.

The FIVE Critical Pivot Table Options

Format Numbers (Not Cells)

Report format: Style and Design Components to Format a Report

The Report: Sum, Count

The Report: Concept Of Data Cubes

The Problem Of Pivot Report not taking New Data

The 4 Quadrants of Data Analysis

 Understanding Dimensions 

  Time Dimension

  Product or Service Dimension

  Geography or Market  or Region Dimension 

  Customer or Prospect Dimension 

Analysis Escalator

A. Historical Analysis

B. Conventional Descreptive or Diagnostic Analysis

• Show Amount as % Of Total

• % of Grand Total or % of Column Total – which is better?

• % Parent Row Totals

• % Parent Column Totals

• % Row Totals

• Variance Analysis – Difference in Value v/s Difference in %

• Running Totals in Value, %

• Ranking Largest to Smallest

• Index

C. Business Descreptive or Diagnostic Analysis

• YOY Analysis

• MTD Analysis

• MTD Weekly Analysis

• YTD Analysis / Growth

Creating Calculated Fields with Simple Calculations

Creating Calcualted Fields with Complex IF Calculations

Creating Calculated Fields with VLOOKUP

Creating Calculated Items and Sets

Creating Profit and Loss Statement  - PnL using Pivot Tables

• Calcualted Fields

• Calculated Items

• Blank Rows with PnL order Sequencing

• Applying Specific Conditional Formatting on Calculated fields

• Genrating PNL Pivot charts

Invoice Aging Analysis with Grouping of Age

• Invoice Age calculation with current date

• Grouping

Pivot Table with Distinct Count

AI Tool  - The ANALYZA DATA TAB Using the Microsoft COPILOT – Natural Language 

AI Tool  - Predective Analysis Using Forecast Sheet

5. The daily grind of data warriors – XLOOKUP / VLookup Functions 

=VLOOKUP()

How to lookup Vertically for data – data in other columns. 

Eliminate the fixed range challenge created by “$A$1:$M$500” range way of VLOOKUP. Use TABLES

Using VLOOKUP to find exact matches

How to provide insurance against current and future “#N/A” errors

Root Cause Analysis of why we get “#N/A” error and solutions to the root cause

#N/A Reason: Unwanted Blank Spaces (bsp)

#N/A Reason: Spaces that are NOT spaces in reality (Non Blank Spaces – nbsp)

#N/A Reason: Transactional Data is text where as lookedup data is numbers.

#N/A Reason: Transactional Data is number where as lookedup data is text

#N/A Reason: Spelling Mistakes

Looking across two different files without “$A$1:$M$500” range way. 

Using VLOOKUP to Group number oriented data like Revenue, Stock, Accounts Receivables, Employee Tenure, etc using APPROXIMATE match component of VLOOKUP.

Using MATCH and INDEX functions

6. Power Pivots – the Future Way of Deep Dive Data Analysis

The Architecture of POWER PIVOT

Enabling the Add-in of Power Pivot 

For Scalability and Usability

• Load Data from Multiple Data Sources

o Excel Files

o Text Files

o Access Database

Relationships

Data and diagram view

Set up of Power Pivot

Creating your first Power Pivot report

Creating Reports Using Power Pivots

• Create Power Pivots – An Alternative for VLOOKUP

• Upload Large Volume DATAsets 

7. Power Query – the Future Way of Transforming DATA – AI Tool

Exploring the Power Query Interface

Your First Transformation Example

 Text Transformations

 Date Transformations

 Number Transformations

Sheet Transformations

 Rows

 Columns

UnPivot DATA

Pivot data 

Merge DATA from Multiple files

Import Data from Various Sources: Excel, Text, Web 

Aggregate or summarize data 

Append Query: Combine one below other 

Combine Different files of similar format from a folder 

Combine different sheets of similar format from a file 

Split Columns 

Transpose Data

8. Logical Functions

=IF()

=OR()

=AND()

=IF(IF(IF)) – Nested IF

9. Date Functions

Is it a Date?

Calculate the exact service length in YEARS, MONTHS, DAYS without dividing by 365! Use function DATEDIF().

Find the number of days between two dates using =NETWORKDAYS.INTL()

 Calander days

 Working Days (without FRI-SAT as weekends)

Working Days (without Public Holidays and FRI-SAT as weekends)

10. Text Functions to clean data

=SEARCH()

=SUBSTITUTE()

=LEN()

=LEFT()

=RIGHT()

=TEXT()

=PROPER()

=TRIM()

=CONCATENATE() (or “&”)

AI Tool: Flash Fill

11. Conditional Formatting – Basics to Advanced

Bars

Highlight Cell Rule

Top-Bottom Rule

Manual Rules

Formula Based CF Rules 

12. Charts - Creative Charts to Advanced

Simple Charts – One Click

Creative Charts

• Using Images – The Silhoute Way

• Using Flags – The Image Way

• Thermometer Chart

• Speedometer

13. The Excel Macros – Automate your Way

The Recorded Macros

Write Your Macro

Create USER Defined Functions

• QUARTER()

• HALF YEAR()

14. Excel NEW Functions 

XLOOKUP

VSTACK

IFS

FILTER

15. Creating Interactive Dashboards Screenshots

Dashboard planning

Use of Filters inside Pivots.

Using Pivot Charts

Why keep formatting all Charts manually – let Excel do this work!

Using Slicers

Multiple Charts being controlled by several Slicers – the inter-activity

Protecting your Dashboard

16. Leveraging AI Tools in Excel 

Automating Tasks with Flash Fill 

Streamlining Data Transformation with Power Query 

Insights and Analysis with AI in Excel 

Visualizing Data with Recommended Charts 

Visualizing Data with Recommended Pivot Table 

Forecasting Trends with Excel’s AI 

17. Leveraging AI Tools such as COPILOT, Julius , Brick AI

1. My Data in Excel

2. Dashboard on my Mobile Device Using Microsoft Power BI – the Journey 

3. Power BI 

• On my Desktop (to use Excel files) On my Web Browser 
• On my Mobile (to show Dashboard) How to get it and install? 

4. Power BI on Desktop 

• Bringing Excel Files to Power BI Getting Excel Data into Power BI 
• Excel Data - Dos and Don’ts / Best Practices Naming of Table 
• Naming of columns 
• Date format only - No columns for Month / Qtr./ Year 
• Data type: 
• Using multiple sheets or multiple files for data. 

5. Making Charts on Desktop Power BI 

• Type of Visualizations Default visuals 
• Custom visuals: The recommendations- Sparkline, Card with State 
• Location Download Process 
• Import into Power BI Desktop Import into Power BI web 
• Suggested visualizations for Finance Data Maps: Filled maps vs Maps 
• Slicers Combo Charts Bar Charts 
• Single Number Card Tile Gauge Chart 
• Matrix 
• Pie and Doughnut Charts Line Charts 
• Scatter & Bubble charts 

6. Frequently Used Formatting in Visuals 

• Data Labels Title 
• Font size 
• Showing in Millions or Thousands - how to Seeing data not visualized - hovering - tool tip Using your Corporate Colors 

7. Interactivity in Power BI Reports / Interactivity amongst Charts 

• Use of Slicers - Page Level only Use of Filters 
• Chart Level Page Level Report level 

8. Combination of Charts Makes a Report

• Saving your Report - the file type ".PBIX" 
• Publishing your Report to Power BI Web - need for login 

9. Power BI Web 

• Use Power BI web on Browser Need for login 
• Power BI Desktop to Power BI Web - the Reports to Dashboard 
• Pinning Charts from Reports to create Tiles in a Dashboard 
• Playing around with Tiles - arrangement, serial order, size - for WEB and for MOBILE. 
• Back to WYSIWYG - See "as on mobile" on the web. 
• Modifying report, dashboard till you get what you want; Adding new Chart 

10. Power BI Web to Power BI APP on your Mobile 

• Log in to Power BI app – the settings 
• Getting used to interface of Power BI with sample dashboards 
• Reports and Dashboard tabs Preview Dashboard on your Mobile 
• Interactivity in Power BI Dashboard -Deep dive for full chart. 
• While Using a Dashboard: Write Comments on a chart and share via WhatsApp 

11. Sharing the Dashboard 

• Within same organization Across organizations

Power BI Advanced with DAX & PBI Service Features

(Contents of this course have been created based on PRACTICAL requirements and INDUSTRY standards) 

Duration: 1 Day (8 hours)

Who should invest time in this course?

The course is apt for professionals who will be Developers of POWER BI. This course is intended for Business Managers, Data Explorers, Data Analysts etc.

Take-away’s from this course?

The Key Take-away’s of this hands-on course, includes,

• Deep Dive understanding of DAX Functions

• The Concept of MEASURES Table

• The Concept of Creating CALENDAR Fact – Table Using DAX Functions

• Row Level Security

• Python Visuals

Course Outline 

1. Functions: Data Analysis Expressions: DAX

Aggregation:

• SUM, AVERAGE

• MIN, MAX

• SUMX (and other X functions)

Counting

• COUNT, COUNTA

• COUNTBLANK, COUNTROWS

• DISTINCTCOUNT, COUNTROWS

Logical

• AND, OR

• NOT

• IF, IFERROR

Text

• FORMAT

• TRIM

• LEFT

• RIGHT

• SUBSTITUTE

Date 

• TODAY

• DATEDIFF

• NOW

• UTCTODAY

• UTCNOW

Additional DAX Functions

• DIVIDE

• COLUMN

• RELATED

• RELATEDTABLE

• SWITCH

• HASONEVALUE

• VALUES

• CALCULATE

• SUMX

• VAR 

• ADDCOLUMNS

Time intelligence

• Year-to-date total: TOTALSYTD

• Month-to-date total: TOTALSMTD

• Quarte-to-date total: TOTALSQTD

• SAMEPERIODLASTYEAR

• YEAR OVER YEAR CHANGE

• MONTH-OVER-MONTH CHANGE

2. The Concept of MEASURES Table

• Creating Measures Table

3. The Concept of Creating CALENDAR Fact – Table Using DAX Functions

• STATIC : Creating Calendar Table from Excel

• STATIC: Creating Calendar Table Using CALENDAR DAX Function

• DYNAMIC: Creating Calendar Table Using CALENDARAUTO Function

• STATIC/ DYNAMIC: Ceating Table Using ADDCOLUMNS Function

4. Creating Dynamic Parametrized Visuals

• Parameters 

• X-AXIS

• Y-AXIS

5. RLS – Row Level Security

• Row Level Security – Static  with Single Filter

• Row Level Security – Static  with Multiple Filters

• Row Level Security – Dynamic  with Single Filter using

o Security Table

o USERPRINCIPALNAME() Function

• Row Level Security – Dynamic  with Multipe Filters using

o Security Table

o DATE Modeling Relationship

6. Python Visuals

• Installation of Python

• Installation of Python Packages

o PANDAS

o MATPLOTLIB

o SEABORN

o NUMPY

• Fetching DATA from Sample Python Datasets using Pythin scripts

o TIPS

o PENGUINS

• Creating Python Visuals 

o BOXPLOT

o SCATTER PLOT

o VIOLIN PLOT

o PAIR PLOT

o SWARM PLOT

DAX Functions

(Contents of this course have been created based on PRACTICAL requirements and INDUSTRY standards) 

Duration: 1 Day (8 hours)

Course Outline 

1. Functions: Data Analysis Expressions: DAX

Aggregation:

• SUM, AVERAGE

• MIN, MAX

• SUMX (and other X functions)

Counting

• COUNT, COUNTA

• COUNTBLANK, COUNTROWS

• DISTINCTCOUNT, COUNTROWS

Logical

• AND, OR

• NOT

• IF, IFERROR

• SWITCh

Text

• FORMAT

• TRIM

• LEFT

• RIGHT

• SUBSTITUTE

FILTER

• FILTER

• NESTED FILTER

Date 

• TODAY

• DATEDIFF

• NOW

• UTCTODAY

• UTCNOW

Additional DAX Functions

• DIVIDE

• COLUMN

• RELATED

• RELATEDTABLE

• SWITCH

• HASONEVALUE

• VALUES

• CALCULATE

• SUMX

• VAR 

• ADDCOLUMNS

• SELECTEDCOLUMNS

Time intelligence

• Year-to-date total: TOTALSYTD

• Month-to-date total: TOTALSMTD

• Quarte-to-date total: TOTALSQTD

• SAMEPERIODLASTYEAR

• YEAR OVER YEAR CHANGE

• MONTH-OVER-MONTH CHANGE

• QUARTER-on-QUARTER CHANGE

2. The Concept of MEASURES Table

• Creating Measures Table

3. The Concept of Creating CALENDAR Fact – Table Using DAX Functions

• STATIC : Creating Calendar Table from Excel

• STATIC: Creating Calendar Table Using CALENDAR DAX Function

• DYNAMIC: Creating Calendar Table Using CALENDARAUTO Function

• STATIC/ DYNAMIC: Ceating Table Using ADDCOLUMNS Function

4. Creating Dynamic Parametrized Visuals

• Parameters 

• X-AXIS

• Y-AXIS

Coming Soon

“Microsoft Power Automate Desktop & Cloud Flows - Foundations” 

1 Full Day (8 hours)

(Contents of this course have been created based on PRACTICAL requirements and INDUSTRY standards) 

1. An Introduction to Power Automate

What is Microsoft Power Automate?

The benefits of automation

How to get to Power Automate Desktop ?

How to Access Power Automate Cloud?

2. Get started with Power Automate for desktop - PAD

Take your first steps with Power Automate for desktop

• Introduction to Power Automate for desktop – The PAD 

• Console overview – The Interface 

• Flow designer overview

• Create a flow in the flow designer

• An understadning of Action Library

• 30 Categories & 367 Actions

• Variables – Input, Output & Flow Variables 

• Manage UI elements and images

3. Work with different technologies in Power Automate for desktop

Automate system, workstation, and services actions in Power Automate for desktop

• Introduction

• System and workstation actions

• Services actions

Communicate using message boxes in Power Automate for desktop flows

• Introduction to message boxes

• Message box actions

• Use message boxes to communicate

Use Power Automate for desktop to interact with windows and applications

• Introduction

• UI elements – Using Recorder

• Window handling and element interaction

• UI Automation

• Generate Power Automate for desktop flows by recording

• Introduction to desktop and web recorder

• Create a desktop flow using the recorder

• Exercise - Use the recorder to automate web applications

Control file and folder handling in Power Automate for desktop

• Introduction

• Folder Actions

• File Actions

Excel automation in Power Automate for desktop

• Introduction

• Launch, save, and close an Excel file

• Read from an Excel document

• Write to an Excel worksheet

• Additional actions and features

• Manage worksheets

• Read from a CSV and write to Excel

• Apply Filters

• Remove Filters

Automating email handling in Power Automate for desktop

• Introducing email automation

• Automate webmail services

• Automate Outlook

• Automate Exchange servers

• Exercise - Confirm the submission of support tickets

Web automation in Power Automate for desktop

• Introduction to Web Automation

• Launch, close, and handle browsers

• Handle web pages and forms

• Extract data from web pages

• Direct web access and scripting

• Exercise - Extract stocks from MSN

Text manipulation in Power Automate for desktop

• Introduction

• Text handling actions

• Date time actions

• Exercise – Text actions

Sharepoint online foe Business Actions

• Create File

• Create Folder

• Delete Actions

• Working with Lists etc

OneDrive for Business PAD Actions

• Create File

• Create Folder

• Delete File 

Microsoft Teams PAD Actions

• Create Team

• Create Channel

• Post Messages on Channel/ etc

• Teams Approvals

4. Work with Power Automate Desktop – Conditionals

Handling Variables

• Introducing variables

• Create, edit, and use variables

• Variable data types

• Input and output variables

• Variable manipulation

• Exercise - Create, access and manipulate variables

Automate repetitive tasks using loops - Basics

• Introduction to loops

• Loop actions

• Simple loop actions

Adjust process behavior using conditional actions – Basics of IF and Else

• What is conditional actions

• Conditional actions

• The If group of actions

5. Getting Started with Power Automate Cloud - Basics

Using Power Automate templates 

Navigating in Power Automate 

Editing a Power Automate 

Publish and trigger a Power Automate 

Turn off or delete a Power Automate 

Building processes in Office 365 

• Track MS Forms Responses & Save in Excel – Notify via email and Teams

• Create a Team with a channel 

• Build an absence business process – Leave Application

• Append Multiple Excel Sheets into Single Excel sheet

• Populate Word Template from Excel data - Notify via email and Teams

Coming Soon

Coming Soon

Coming Soon