Data Connectivity:

Power BI can connect with a wide variety of data sources. For any data analysis project, data consumption process starts with an understanding of business requirements and data sources available to us. Data connectivity mode is defined after analyzing all these above

• Installation
• Overview of Power BI Desktop
• Data Sources in Power BI Desktop
• Connecting to a data Sources (Excel, Web, SQL, Json, Enter data)

• Introduction to using Excel data in Power BI
• Connection Modes: Import, Direct query and Live mode

The Power Query Editor in Power BI is a tool that allows users to connect to data sources, transform data, and load it into Power BI Desktop. It’s a graphical user interface that helps users perform data extraction, transformation, and loading (ETL) operations Query Editor in Power BI

• • Data Connections, Data source settings and Data Types
• • Refresh uses, diff b/w duplicate and reference
• • Column Operations: Choose and Remove
• • Row Operations: Keep, Remove, Transpose, Reverse, Count
• • Sorting, Group By, Split Column and Replace values
• • Combining data: Merge and Append queries
• • Joins (Left Outer, Right Outer, Inner, Full Outer, Left Anti, Right Anti)
• • Manage parameters and combine files
• • Fill Data, Pivot and Unpivot
• • Columns from Examples
• • Conditional and Custom columns
• • Combining Data – Merging and Appending
• • Close & Apply, Close, Apply uses

Data modeling is the process of analyzing and defining all the different data types your business collects and produces, as well as the relationships between those bits of data

• • Importance of data modeling view
• • Ways of creating Relationships
• • Creating Relationships and Auto Detecting Relationships
• • Cross filter direction uses
• • Cardinality: One to One, Many to One, One to Many and Many to Many
• • Schemas: Star and Snowflake

• • Conditional Formatting – Rules, Gradient and Field Value
• • Pane Manager: Data, Build, Format, Bookmarks, Selection, Performance Analyzer and Sync Slicers
• • Edit Interactions
• • Drill Down
• • Drill Through
• • Basic and Advance Tooltips
• • Navigators: Bookmark and Page
• • Action types: Back, Bookmark, Drill Through, Page Navigation, Q&A, Web URL, Apply and Clear
• • Shapes, text boxes, Buttons and images
• • Filters: All Pages, single Page and Visual level
• • Page layout and formatting
• • Bookmarks & Selections
• • Enabling Small multiples
• • Importance of Legend
• • Reference Line
• • Error Bars
• • Gridlines and Lock objects
• • Changing Themes and getting Themes from market place
• • Hiding, Duplicating and creating new pages
• • Setting RLS (Static and Dynamic ways)
• • Sorting a column based on other
• • Data Types and Format

In this chapter, you’ll learn how to create customized calculations using Microsoft’s formula language, DAX (Data Analysis Expressions). Very similar to how formulas are written in Excel, you’ll learn how to create calculated columns and tables—and when you should use them. You’ll then progress to writing powerful calculated measures, leveraging Power BI’s Quick Measures, to create complex DAX calculations like year over year change.

• • Essential concepts in DAX
• • Why is DAX important?
• • DAX Syntax
• • Data Types in DAX
• • Math and Trig: ABS, Ceiling, Floor, Even, Odd, Fact, MOD, Round, SQRT, Randbetween
• • Aggregations: Sum, SumX, Average, AverageX, Count, CountX, DistinctCount, Min, Max
• • Date & Time: Calendar, Date, Day, Month, Year, Now, Today, Weekday, Weeknum
• • Filter: All, AllExcept, Allcrossfiltered, Calculate, Filter, Lookupvalue, SelectedValue
• • Table: Summarize, Union, GroupBY, NaturalLeftOuterJoin, NaturalInnerJoin
• • Text: CombineValues, Concatenate, Exact, Find, Format, Left, Len, Lower, Mid, Right, Search, Trim, Unichar, Upper
• • Time Intellegence: DateAdd, DatesBetween, DatesInPeriod, DatesMTD, DatesYTD, LastDate, TotalMTD, TotalYTD
• • Information: UserPrincipalName, IsFiltered
• • Logical: IF, Switch, OR, And, Not
• • Ranking and rank over groups
• • Filter and evaluation context
• • Context interactions
• • Create calculated tables, Columns and measures
• • Visual level calculation
• • DAX Variable
• • Dax query view

The Microsoft Power BI service (app.powerbi.com), sometimes referred to as Power BI online, is the SaaS (Software as a Service) part of Power BI. In the Power BI service, dashboards help you keep a finger on the pulse of your business. Dashboards display tiles, which you can select to open reports for exploring further. Dashboards and reports connect to datasets that bring all the relevant data together in one place.

• Introduction to Power BI Service
• Account creation
• Licensing types
• Introduction to using workspaces
• Roles: Viewer, Contributor, Member, Admin
• Sharing and revoking report access to users
• Downloading the power bi reports
• Uses of Analyzing in Excel option
• Embedding the report to power point
• Exporting the report into PDF and PPT as images
• Edit all possibilities
• Report Subscription uses
• Installing Gateways
• Scheduling refresh and Parameters
• Dashboard vs. Reports
• Creating Dashboards
• Configuring a Dashboard
• Data caching and refresh
• Power BI Q&A
• Ask questions of your data with natural language
• Power BI embedded into websites
• Build apps

• In-Class Project:
  • Comprehensive project covers all essentials of the course.
  Note: You do not need any prior knowledge to benefit from this Microsoft Power BI online training.

▪ Data Definition Language (DDL)
▪ Data Manipulation Language (DML)
▪ Data Query Language (DQL)
▪ Data Control Language (DCL)
▪ Transaction Control Language (TCL)
▪ Database
▪ Creating Database
▪ Altering Database
▪ Deleting Database
▪ Constrains
▪ Procedural Integrity Constraints
▪ Declarative Integrity Constraints
▪ Not Null, Unique, Default and Check constraints
▪ Primary Key and Referential Integrity or foreign key constraints
▪ Data Types In SQL
▪ Table
▪ Creating Table
▪ Altering Table
▪ Deleting Table

▪ Insert
▪ Identity
▪ Creating A Table From Another Table
▪ Inserting Rows From One Table To Another
▪ Update
▪ Computed Columns
▪ Delete
▪ Truncate
▪ Differences Between Delete and Truncate

▪ Select
▪ Where clause
▪ Order By Clause
▪ Distinct Keyword
▪ Isnull() function
▪ Column aliases
▪ Predicates
▪ Between … And
▪ In
▪ Like
▪ Is Null

▪ Scalar Functions
▪ Numeric Functions
▪ Character Functions
▪ Conversion Functions
▪ Date Functions
▪ Aggregate Functions
▪ Convenient Aggregate Functions
▪ Statistical Aggregate Functions
▪ Group By and Having Clauses
▪ Super Aggregates
▪ Over(partition by …) Clause
▪ Ranking Functions
▪ Common Table Expressions (CTE)

▪ Union
▪ Intersect
▪ Except

▪ Inner Join
▪ Equi Join
▪ Natural Join
▪ Non-Equi Join
▪ Self Join
▪ Outer Join
▪ Left Outer Join
▪ Right Outer Join
▪ Full Outer Join
▪ Cross Join

▪ Single Row Sub Queries
▪ Multi Row Sub Queries
▪ Any or Some
▪ ALL
▪ Nested Sub Queries
▪ Co-Related Sub Queries
▪ Exists and Not Exists

▪ Clustered Index
▪ NonClustered Index
▪ Create , Alter and Drop Indexes
▪ Using Indexes

▪ Login Creation
▪ SQL Server Authenticated Login
▪ Windows Authenticated Login
▪ User Creation
▪ Granting Permissions
▪ Revoking Permissions
▪ Roles

▪ Purpose Of Views
▪ Creating , Altering and Dropping Indexes
▪ Simple and Complex Views
▪ Encryption and Schema Binding Options in creating views

▪ Introduction
▪ Begin Transaction
▪ Commit Transaction
▪ Rollback Transaction
▪ Save Transaction
▪ Role Of Log File In Transaction Management
▪ Implicit Transactions