Power BI + Data Analytics Training in Kayathar

Introduction

• The Relational Model

Understanding Basic SQL Syntax

• Basic SQL Commands – SELECT
• Basic SQL Commands – INSERT
• Basic SQL Commands – UPDATE
• Basic SQL Commands – DELETE
• Querying Data with the SELECT Statement
•  The SELECT List
• SELECT List Wildcard (*)
• The FROM Clause
• How to Constrain the Result Set
• DISTINCT and NOT DISTINCT

Filtering results with Where Clause

• WHERE Clause
• Boolean Operators
• The AND Keyword
• The OR Keyword
• Other Boolean Operators BETWEEN, LIKE, IN, IS, IS NOT

Shaping Results with ORDER BY and GROUP BY

• ORDER BY
• Set Functions
• Set Function And Qualifiers
• GROUP BY
• HAVING clause

Matching Different Data Tables With JOINS

• CROSS JOIN
• INNER JOIN
• OUTER JOINs
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• SELF JOIN

Creating Database Table Stamp

• CREATE DATABASE
• CREATE TABLE
• NULL Values
• PRIMARY KEY
• CONSTRAINT
• ALTER TABLE
• DROP TABLE

Introduction to Power BI:

• Get Started with Power BI
• Overview: Power BI data sources
• Upload a local CSV file
• Connect to Excel data

Power BI Desktop:

• Overview of Power BI Desktop
• Data Sources in Power BI Desktop
• Connecting to a data Sources
• Query Editor in Power BI
• Query Ribbon
• Clean and Transform your data with Query Editor
• Combining Data – Merging and Appending
• Cleaning irregularly formatted data
• Views in Power BI Desktop
• Modelling Data
• Manage Data Relationship
• Template Apps
• Cross Filter Direction
• Create calculated tables and measures

Viz and Tiles:

• Overview: Visualizations
• Create a new report using Visualization
• Format a visualization
• Create chart visualizations
• Use text, map, and gauge visualizations and save a report
• Use a slicer to filter visualizations
• Sort, copy, and paste visualizations
• Download and use a custom visual from the gallery

Reports and Dashboards:

• Modify and Print a Report
• Rename and delete report pages
• Add a filter to a page or report
• Set visualization interactions
• Print a report page
• Send a report to PowerPoint
• Create a Dashboard
• Create and manage dashboards
• Pin a report tile to a dashboard
• Pin a live report page to a dashboard
• Pin a tile from another dashboard
• Pin an Excel element to a dashboard
• Manage pinned elements in Excel
• Add a tile to a dashboard
• Build a dashboard with Quick Insights
• Set a Featured (default) dashboard
• Ask Questions about Your Data
• Ask a question with Power BI Q&A
• Tweak your dataset for Q&A
• Enable Cortana for Power BI

Publishing Workbooks and Workspace:

• Share Data with Colleagues and Others
• Publish a report to the web
• Manage published reports
• Share a dashboard
• Create an app workspace and add users
• Use an app workspace
• Publish an app
• Create a QR code to share a tile
• Embed a report in SharePoint Online

DAX functions:

• New Dax functions
• Date and time functions
• Time intelligence functions
• Filter functions
• Information functions
• Logical functions
• Math-trig functions
• Parent and child functions
• Text functions

Power Query: Text Functions

• Text Functions
• Merge Columns
• Split and Trim
• Upper, Lower and ProperCase
• Prefix and Suffix
• Left, Right and Mid Functions
• Extract Text with Delimiters

Power Query: Date Functions

• Date Functions
• Year, Quarter, Month and Day Functions
• Find Difference between Dates
• Month and Day Name
• Day, Week of Month, Year
• Extract Date, Time in Power Query
• Calculate Age in Power Query
• Day of Year, Quarter, Month in Power Query

Power Query: Number Functions

• Number Functions
• Basic Number Functions in Power Query
• Percentage, Percent Of, Module
• Round Functions
• IsEven, IsODD, Sign in Power Query

Power Query: Append Files

• Append Multiple CSV Files in a Folder
• Append Multiple Excel Sheets, Tables
• Append Excel Sheets or Tables with Different Columns
• Append Multiple Excel Files from a Folder
• Append Different Data Sources

Power Query: Merge Files

• Merge Files and Tables
• Merge Sheets or Tables
• Merge Data from Multiple Excel Files or Workbooks
• Merge Data from Different Data Sources
• Merge Data having Multiple Criteria in Power BI

Power Query: Conditional Columns

• Conditional Column and Column from Example in Power BI
• Column from Examples – Split Text
• Column from Examples – Merge Columns
• Column from Examples – Date
• Column from Examples – Alphanumeric
• Conditional Column – One Column
• Conditional Column – Two columns
• Conditional Column – Compare two columns
• Conditional Column – on Dates
• Fill Down in Power BI
• Grouping
• Transpose
• Unpivot
• Data Types
• Replace Errors and Values
• Keep and Remove Rows
• Add, Remove and Goto Columns

• Using Functions – Sum, Average, Max,Min, Count, Counta
• Applying Absolute (Fixed) Referencing

Mathematical Functions:

• SumIf, SumIfs
• CountIf, CountIfs
• AverageIf, AverageIfs
• SumProduct, Subtotal

LookUp Functions:

• Vlookup / HLookup
• Match
• Dynamic Two Way Lookup
• Creating Smooth User Interface Using Lookup
• Offset
• Index
• Dynamic Worksheet linking using Indirect

Text Functions:

• Upper, Lower, Proper
• Left, Mid, Right
• Trim, Len
• Concatenate
• Find, Substitute

Sorting:

• Sorting on Multiple Fields
• Dynamic Sorting of Fields
• Bring Back to Ground Zero after Multiple Sorts

Filtering:

• Filtering on Text, Numbers & Date
• Filtering on Colors
• Copy Paste while filter is on
• Advanced Filters
• Custom AutoFilter

Import & Export Of Information:

• From Web Page
• Exporting to XML
• Creating Dynamic Dashboards and Reports Using Data on Other Applications
• Using Text To Columns

Data Validation:

• Number, Date & Time Validation
• Text Validation
• List Validation
• Handling Invalid Inputs
• Dynamic Dropdown List Creation using Data Validation

Pivot Tables:

• Creating Simple Pivot Tables
• Basic and Advanced Value Field Setting
• Sorting based on Labels and Values
• Filtering based on Labels and Values
• Grouping based on numbers and Dates
• Drill-Down of Data
• GetPivotData Function
• Calculated Field & Calculated Items

Charts & Pivot Charts:

• Bar Charts / Pie Charts / Line Charts
• Dual Axis Charts
• Dynamic Charting
• Other Advanced Charting Techniques

Excel Dashboard:

• Bar Charts / Pie Charts / Line Charts
• Planning a Dashboard
• Adding Tables to Dashboard
• Adding Charts to Dashboard
• Adding Dynamic Contents to Dashboard

Introduction to Python

• What are Python and the history of Python?
• Unique features of Python
• Python-2 and Python-3 differences
• Install Python and Environment Setup
• First Python Program
• Python Identifiers, Keywords, and Indentation
• Comments and document interlude in Python
• Command-line arguments
• Getting User Input
• Python Data Types
• What are the variables?
• Python Core objects and Functions
• Math Module

Control Statements

• If
• If-elif-else
• while loop
• for loop
• Break
• Continue
• Assert
• Pass
• return

List, Ranges & Tuples in Python

• Introduction
• Lists in Python
• Generators and Yield
• Generators Comprehensions and Lambda Expressions
• Next() and Range()
• Understanding and using Range

Python Dictionaries and Sets

• Introduction to the section
• Python Dictionaries
• More on Dictionaries
• Sets
• Python Sets Examples

Input and Output in Python

• Reading and writing text files
• Writing Text Files
• Appending to Files and Challenge
• Writing Binary Files Manually

Python built-in function

• Python Modules & Packages
• Python User defined functions
• Defining and calling Function
• The anonymous Function

Introduction to Data Analytics with Python

• Overview of Data Analytics and its applications
• Python basics for data analytics
• Introduction to Jupyter Notebook for code and data visualization
• Setting up the Python environment and installing key libraries

Data Handling with Pandas

• Introduction to Pandas: Series, DataFrames, and Data Structures
• Importing, exporting, and handling data with Pandas
• Data cleaning: handling missing data, duplicates, and data transformations
• Data aggregation, grouping, and summarization
• Merging and joining datasets

Numerical Computation with NumPy

• Introduction to NumPy and its importance in data analytics
• Working with arrays, array indexing, and slicing
• Mathematical operations with NumPy
• Array manipulations and reshaping
• Random number generation for data simulation

Data Visualization with Matplotlib

• Introduction to Matplotlib for basic plotting
• Customizing charts (labels, colors, styles)
• Plot types: line, bar, scatter, histogram, etc.