Microsoft Power BI Content

Module1: Power BI for Data Analyst

Day-1

1. What is Data?
2. How Data is Getting Generated?
3. What is Information and Features?
4. What is Database?
5. What is ETL Processing?
6. Challenges Of Data
7. Features Of Power BI

Day-2

8. Introduction to Power BI
9. Power BI Desktop Installation
10. Power BI Desktop – First Experience
11. Introduction to Power BI Components
12. Power BI Components
    • Power Query
    • Power Pivot
    • Power View
    • Power Q&A
13. Getting Data from Excel File
14. Visualization Basics
15. Importance Of Visualizations
16. Basic Charts: Bar & Column Charts
17. Formatting Visual
    • Visual
    • General

Day-3

18. Slicing & Dicing Introduction
19. Filters Pane
20. Scope Of Filters
    • Report Level
    • Page Level
    • Visual Level
21. Modes Of Filtering
    • Basic
    • Advanced
    • Relative
    • Top N

Day-4

22. Slicers Introduction
23. Types Of Slicers
    • Number
    • Text
    • Date
24. Slicer Formatting
25. Sync Slicers
26. Advantage of Sync Slicers
27. Drill-Through Page Filters

Day-5

28. Report Page Tooltip
29. Edit Interactions
    • Highlight
    • Filter
    • None
30. Bookmarks-Saving Stage/Snapshot of Page
31. Grouping & Binning
32. Button Action Items
33. Selection Pane

Day-6

34. Column Family Charts
    • Clustered Column Chart
    • Stacked Column Chart
    • 100% Stacked Column Chart
35. Bar Family Charts
    • Clustered Bar Chart
    • Stacked Bar Chart
    • 100% Stacked Bar Chart
36. Column vs Bar

Day-7

37. Line Chart
38. Combo Charts
    • Primary Axis
    • Secondary Axis
39. Ribbon Chart
40. Waterfall Chart
41. Group Charts
    • Pie Chart
    • Donut Chart
    • Treemap Chart

Day-8

42. Map Visual
    • Using Bing to Find Spots
    • Using Latitude and Longitude
43. Filled Map
44. Shape Map
45. ArcGIS Map
46. Single Card
47. Multi Row Card
48. Adding Custom Visual

Day-9

49. Table Introduction
50. Table – Conditional Formatting
    • Background Color
    • Font Color
    • Data Bars
51. Matrix
    • Rows
    • Columns
    • Indentation
    • Drill-Down on Rows
    • Drill-Down on Columns

Day-10

Module 2: Power Query

52. Installation of Power BI Desktop
53. Introduction to Power Query
    • Query Editor
    • Ribbon
    • Queries
    • Steps in the Bucket
    • Formula Bar
    • Reading Data from Excel File
    • Reading Data from Csv File
    • Auto Detected Datatypes
    • Changing Datatypes
    • Create Sample Report

Day-11

54. Column Operations
    • Choose Columns
    • Removing Columns
    • Datatype Changes
    • Replace Values
    • Fill Down
    • Fill Up
    • Moving Steps
55. Taking Only required Columns
56. Row Operations
    • Filtering on Numbers Data
    • Filtering on Text Data
    • Filtering on Date Data
    • Removing Rows
    • Keeping Rows
    • Advanced Filtering

Day-12

57. Text Transformations
    • Split Column by Delimiter
    • Split Column by Number of Delimiters
    • Merge (Concatenate)
    • Format
    • Difference Between Clean & Trim
    • Extract
    • Exercises
58. Numeric Transformations
    • Add
    • Divide
    • Sub
    • Multiply
    • Is Even
    • Is Odd
    • Rounding
    • Power
    • Quare
    • Exercises

Day-13

59. Date Transformations
    • Year
    • Start of Year
    • End of Year
    • Quarter
    • Start of Quarter
    • End of Quarter
    • Month
    • Start of Month
    • End of Month
    • Days In Month
    • Name of Month
    • Adding Date from Date Time Column
    • Adding Time from Date Time Column
    • Age
    • Time Transformation-Hour, Minute, Second

Day-14

60. Add Column Transformation
    • Add Column vs Transform
    • Index Column
    • Colum By Examples
    • Add Custom Column
61. Combine Queries
    • Introduction
    • Inner
    • Left Outer
    • Right Outer
    • Full Outer
    • Left Anti & Right Anti
    • Best Practices
    • Fuzzy Merging

Day 15

62. Reading Data from Multiple Files
63. Reading Data from Website
64. Data Appending
65. Data Aggregation
66. Data Pivoting
67. Data Unpivoting

Day-16

Module 3: SQL Server Basics

68. Introduction to SQL Server
69. Installation of SQL Server
70. Installation of SSMS
71. Restoring Sample Database
72. Overview of Database

Day-17

73. Data Querying from Table
74. Order of SELECT Statement
75. Execution Order of SELECT Statement
76. Taking Required Columns
77. WHERE clause

Day-18

78. Functions Introduction
79. Numeric Functions
80. Text Functions

Day-19

81. Date Functions
82. GROUP BY
83. Aggregate Functions

Day-20

84. Data Joining Introduction
    • Inner Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join
    • Self-Join

Day-21

85. Writing SELECT in Place of Table Name
86. Common Table Expressions

Day-22

87. Generating Row Numbers
88. Generating Ranks
89. Windows Functions
    • ROW_NUMBER()
    • RANK()
    • DENSE_RANK()

Day-23

90. Writing Complex Queries
    • With Joins
    • Sub Queries
    • Common Table Expressions (CTE’s)
    • Temporary Tables
    • Table Variables
91. Creating Stored Procedure
    • Without Parameters
    • With Parameters

Day-24

92. Reading Data from SQL Server
    • Import
    • Direct Query
    • Import vs Direct Query

Day-25

Module 4: Data Modelling with Power BI

93. Introduction to Power Pivot
94. Star Schema
    • Fact Tables
    • Dimension Tables
95. Relationship Between Tables
96. Types of Relationships
    • One to One
    • One to Many
    • Many to One
    • Many to Many

Day-26

97. Star Schema – Designing
98. Creating Relationships
    • Auto Detect
    • Drag & Drop
    • Manage Relationships
99. Creating All Measures
100. Apply Best Practices to Modelling

Day-27

101. Snowflake Schema – Introduction
102. Snowflake Schema – Designing
103. Converting Snowflake to Star

Day-28

104. Integrated Schema
     • Star Schema + Star Schema
     • Star Schema + Snowflake Schema
     • Snowflake Schema + Snowflake Schema
105. Integrated Schema – Designing
106. Cross Filter Directions
     • Single
     • Both

Day-29

107. Calculations Introduction
     • New Column
     • New Measure
     • New Table
108. DAX Introduction
     • Syntax of DAX
     • Naming in DAX
     • Operators & Operands
     • Logical Operations
     • Over View of Functions in DAX
109. Math Functions
     • ASB()
     • INT()
     • DIVIDE()
     • POWER()
     • ROUND
     • ROUNDDOWN
     • ROUNDUP

Day-30

110. Text Functions
     • UNICODE()
     • UNICHAR()
     • LOWER()
     • UPPER()
     • CONCATENATE()
     • COMBINEVALUES()
     • LEFT()
     • RIGHT()
     • FIND()
     • SEARCH()
     • MID()
     • LEN()

Day-31

111. Getting Parts of Text
     • Without Variables
     • With Variables
112. Other Text Functions
     • EXACT
     • TRIM()
     • REPT
     • REPLACE
     • SUBSTITUTE()

Day-32

113. Date Functions
     • DATE()
     • CALENDAR()
     • CALENDARAUTO()
     • YEAR()
     • MONTH()
     • DAY()
     • WEEKNUM()
     • QUARTER()
     • EOMONTH()
     • ENDOFMONTH()
     • ENDOFQUARTER()
     • ENDOFYEAR()
     • STARTOFMONTH()
     • STARTOFQUARTER()
     • STARTOFYEAR()
114. Time Functions
     • TODAY()
     • NOW()
     • HOUR()
     • MINUTE()
     • SECOND()

Day-33

115. Logical Functions
     • OR()
     • AND()
     • NOT()
     • IF()
     • TRUE()
     • FALSE()
     • EAGER()
     • SWITCH()
     • COALESC()

Day-34

116. Information Functions
     • CONTAINGSTRING()
     • CONTAININGSTRINGEXACT()
     • HASONEFILTER()
     • ISFILTER()
     • ISBLANK()
     • ISEVEN()
     • ISODD()
     • ISFILTERED()
     • ISNUMBER()
     • ISTEX()
     • SELECTEDMEASURE()
     • USERNAME
     • USERPRINCIPLENAME
117. Filter Functions
     • ALL()
     • FILTER()
     • CALCULATETABLE()
     • KEEPFILTERS()
     • REMOVEFILTERS()
     • LOOKUPVALUES()
     • SELECTEDVALUES()
     • CALCULATE()
     • ALLSELECTED()
     • ALLEXCEPT()

Day-35

118. Aggregate Functions
     • SUM()
     • COUNT()
     • MIN()
     • MAX()
     • AVERAGE()
     • SUMX()
     • MINX()
     • MAX()
     • AVARAGEX()
     • COUNTX()

Day-36

119. Time Intelligence Functions
     • DATESMTD()
     • DATESQTD()
     • DATESYTD()
     • PREVIOUSMONTH()
     • PREVIOUSQUARTER()
     • PREVIOUSYEAR()
     • NEXTMONTH()
     • NEXTQUARTER()
     • NEXTYEAR()
     • TOTALMTD()
     • TOTALQTD()
     • TOTALYTD()
     • SAMEPERIODLASTYEAR
120. Other Functions
     • SUMMARIZE()
     • ADDCOLUMNS()
     • SELECTEDCOLUMNS()
     • SUMMARIZEDCOLUMNS()

Day-37

Module 5: Power BI Administration

121. Power BI Components
122. Power BI Service Components
     • Dataset
     • Report
     • Dashboard
123. Workspace
124. Publish
125. What is Gateway
     • Enterprise
     • Personal
     • Installing Gateway

Day-38

126. Import Data Schedule Refresh
127. DirectQuery Connection
128. LiveConnection

Day-39

129. Creating Report with Existed Dataset
     • By Power BI Service
     • By Power BI Desktop
130. Sharing
     • Report Sharing
     • Dashboard Sharing
     • Power BI Apps
     • Publish to Web
131. Security

Day-40

132. Microsoft Fabric Introduction
133. Creating Lakehouse
134. Creating Model
135. Getting Data from Lakehouse with Power BI Desktop
136. Creating Measures

Day-41

137. Dataflows in Microsoft Fabric
138. Create Model on AdventureWorksDW
139. Create Measures in the LakeHouse

Day-42

140. Project Explanation

Day-43

141. Project Implementation – Phase-I

Day-44

142. Project Implementation – Phase-II

Day-45

143. Resume Preparation
144. Roles & Responsibilities
145. Interview Questions – Discussion
146. Miscellaneous Items