Data analytics · Course by Ravindra Bagale
Power BI
Microsoft Power BI: Complete Study Guide: From Beginner to Job-Ready
Getting data, Power Query and data cleaning A–Z, parameters, data modelling, DAX, visuals and formatting, maps, drill, tooltips, bookmarks, filters and slicers, dashboards, the Power BI Service, security, performance, an end-to-end project and interview questions, with Blinkit and Amazon Now sample data (fictional, for learning).
33 chapters294 concepts
Course outline
Click a chapter to see its concepts. Each concept has its own page.
How to Read This Book – start here
Chapters 1–17
4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection 9 concepts
- 4.1 Database Terms in Simple Words
- 4.2 Connecting to Microsoft SQL Server
- 4.3 Advanced Options and Native SQL Queries
- 4.4 Query Folding with Databases and View Native Query
- 4.5 Connecting to MySQL
- 4.6 DirectQuery in Practice
- 4.7 Import vs DirectQuery vs Live Connection – Clearly Explained
- 4.8 The On-premises Data Gateway
- 4.9 Data Source Settings, Changing Sources and Privacy Levels
5. Web Data, APIs and Google Sheets 7 concepts
- 5.1 The Web Connector
- 5.2 Scraping an HTML Table (Worked Example)
- 5.3 Many Pages: Pagination with a Parameter and a Custom Function
- 5.4 JSON APIs with Web.Contents and Json.Document
- 5.5 Google Sheets Connector
- 5.6 Alternative: Publish a Google Sheet as CSV and Use the Web Connector
- 5.7 Refreshing Web Data and Ethics
6. Power Query Essentials 11 concepts
7. Data Cleaning A–Z in Power Query 25 concepts
- 7.1 A Cleaning Workflow You Can Repeat
- 7.2 Data Profiling Tools
- 7.3 Messy Excel Exports: Title Rows, Headers, Footers and Transpose
- 7.4 Removing Duplicates (Full Row and by Key)
- 7.5 Blanks and Nulls: Remove, Replace, Fill Down / Fill Up
- 7.6 Extra Spaces, Non-printable Characters and Text Case
- 7.7 Wrong Data Types and Indian Date Formats (Using Locale)
- 7.8 Numbers Stored as Text, ₹ Symbol and Commas
- 7.9 Inconsistent Spellings and Standardisation
- 7.10 Split Columns (All Six Methods, Plus Split into Rows)
- 7.11 Merge Columns
- 7.12 Extract: Length, First/Last Characters, Range and Delimiters
- 7.13 Phone Numbers, E-mails and PIN Codes
- 7.14 Dates and Times: Parts, Split DateTime, UTC to IST, Age and Duration
- 7.15 Errors: Remove, Replace, Keep and try … otherwise
- 7.16 Outliers and Invalid Values
- 7.17 Unpivot and Pivot
- 7.18 Keep / Remove Rows, Sort and Filter
- 7.19 Rounding
- 7.20 Combine Files from a Folder (Quick Version)
- 7.21 Append Queries (Stack Rows)
- 7.22 Merge Queries and All Join Kinds
- 7.23 Group By (Basic and Advanced)
- 7.24 Conditional Column, Index Column and Column From Examples in Cleaning
- 7.25 Case Study: Cleaning a Messy Blinkit Export End to End
8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns 13 concepts
- 8.1 The Add Column Tab at a Glance
- 8.2 Column From Examples
- 8.3 Custom Column
- 8.4 Invoke Custom Function
- 8.5 Conditional Column
- 8.6 Index Column
- 8.7 Duplicate Column
- 8.8 From Text: Format, Merge Columns, Extract, Parse
- 8.9 From Number: Statistics, Standard, Scientific, Trigonometry, Rounding, Information
- 8.10 From Date & Time: Date, Time, Duration
- 8.11 DAX Calculated Columns
- 8.12 Calculated Columns vs Measures in Depth
- 8.13 Implicit vs Explicit Measures, Measure Tables, Display Folders and Formatting
9. Get Data from Folder: Monthly Bank Statements Walkthrough 8 concepts
- 9.1 The Files and Their Layout
- 9.2 Connect to the Folder
- 9.3 Filter Out Hidden, Temporary and Unwanted Files
- 9.4 Combine Files and the Helper Queries
- 9.5 Clean One File in Transform Sample File
- 9.6 Add Month from Source.Name and Categorise Transactions
- 9.7 A File with an Extra Column or Different Headers
- 9.8 Automatic Refresh When a New Month Arrives
10. Parameters in Power Query (and Other Kinds of Parameters) 9 concepts
- 10.1 What Is a Parameter and Why Use It?
- 10.2 Creating a Parameter (Manage Parameters Dialog)
- 10.3 Parameterising a File or Folder Path
- 10.4 Parameterising Server and Database (Dev → Prod)
- 10.5 Date-range Parameters for Filtering
- 10.6 Using Parameters Elsewhere in M
- 10.7 Templates (.pbit) with Parameters
- 10.8 Incremental Refresh with RangeStart and RangeEnd (Overview)
- 10.9 What-if Parameters and Field Parameters Are Different
12. Data Hierarchies and Groups 7 concepts
13. DAX: Data Analysis Expressions 19 concepts
- 13.1 What is DAX?
- 13.2 Calculated Columns vs Measures (Recap)
- 13.3 Evaluation Context
- 13.4 DAX Syntax
- 13.5 Base Measures for the Quick-Commerce Model
- 13.6 Aggregation Functions
- 13.7 Logical Functions
- 13.8 CALCULATE – The Most Important Function
- 13.9 FILTER
- 13.10 ALL, ALLEXCEPT, REMOVEFILTERS, ALLSELECTED
- 13.11 RELATED and RELATEDTABLE
- 13.12 USERELATIONSHIP
- 13.13 VALUES, SELECTEDVALUE, HASONEVALUE
- 13.14 Text Functions
- 13.15 Date Functions
- 13.16 Time Intelligence
- 13.17 Ranking: RANKX and TOPN
- 13.18 Variables: VAR and RETURN
- 13.19 Other Useful Functions
14. Visuals and Their Formatting 23 concepts
- 14.1 Creating a Visual Step by Step
- 14.2 The Format Pane: Visual Tab, General Tab and Analytics
- 14.3 Choose the Right Chart
- 14.4 Bar and Column Charts (Stacked, Clustered, 100% Stacked)
- 14.5 Line, Area and Stacked Area Charts
- 14.6 Line and Column Combo Charts
- 14.7 Ribbon Chart
- 14.8 Waterfall Chart
- 14.9 Funnel Chart
- 14.10 Scatter and Bubble Charts (with Play Axis)
- 14.11 Pie, Donut and Treemap
- 14.12 Map, Filled Map, Shape Map and Azure Map (Summary)
- 14.13 Gauge
- 14.14 Card, Card (new) and Multi-row Card
- 14.15 KPI Visual
- 14.16 Slicer
- 14.17 Table
- 14.18 Matrix
- 14.19 AI Visuals: Decomposition Tree, Key Influencers, Q&A, Narrative
- 14.20 Text Box, Image, Shapes and Buttons
- 14.21 R and Python Visuals, and Custom Visuals from AppSource
- 14.22 Small Multiples and Visual Calculations (Quick Look)
- 14.23 The Analytics Pane
15. Maps: Showing Maharashtra on a Map 7 concepts
Chapters 18–33
20. Dynamic Text and Dynamic Colour (Conditional Formatting) 8 concepts
- 20.1 The fx Button
- 20.2 Dynamic Titles with Measures
- 20.3 Showing Several Selected Cities (CONCATENATEX)
- 20.4 Text Box with Dynamic Values and Smart Narrative
- 20.5 Cards with Dynamic Labels
- 20.6 Conditional Formatting: Rules, Gradient and Field Value
- 20.7 Data Bars, Icons and Web URLs
- 20.8 KPI-based Colouring in Charts and Cards
22. Filters and Slicers 12 concepts
- 22.1 The Filters Pane and Its Levels
- 22.2 Basic and Advanced Filtering
- 22.3 Measure Filters (Visual Level)
- 22.4 Top N Filter
- 22.5 Relative Date and Relative Time Filters
- 22.6 Locking, Hiding, Renaming and Formatting Filters
- 22.7 Slicers: Styles and Settings
- 22.8 Clearing Selections
- 22.9 Sync Slicers Across Pages
- 22.10 Edit Interactions: Filter, Highlight, None
- 22.11 Slicer vs Filter: When to Use Which
- 22.12 How All Filters Combine
24. Reports and Dashboards 9 concepts
28. End-to-End Project: Blinkit Quick-Commerce Dashboard 9 concepts
- 28.1 Business Requirements
- 28.2 Step 1 – Prepare the Raw Data
- 28.3 Step 2 – Get Data and Transform in Power Query
- 28.4 Step 3 – Build the Data Model
- 28.5 Step 4 – Create the Measures
- 28.6 Step 5 – Design the Report Pages
- 28.7 Step 6 – Security
- 28.8 Step 7 – Check Performance and Quality
- 28.9 Step 8 – Publish and Automate
29. Practice Exercises and Final Assignment 13 concepts
- 29.1 Modules 1–2: Introduction and Interface
- 29.2 Modules 3–5: Getting Data, Databases, Web and Google Sheets
- 29.3 Modules 6–7: Power Query Essentials and Data Cleaning
- 29.4 Module 8: Add Column, Calculated Columns and Measures
- 29.5 Modules 9–10: Folder Combine and Parameters
- 29.6 Modules 11–12: Data Modelling, Hierarchies and Groups
- 29.7 Module 13: DAX (with answer hints)
- 29.8 Modules 14–15: Visuals, Formatting and Maps
- 29.9 Modules 16–19: Drill, Drill Through, Tooltips and Bookmarks
- 29.10 Modules 20–22: Dynamic Text and Colour, Dynamic Charts, Filters and Slicers
- 29.11 Modules 23–24: Design, Reports and Dashboards
- 29.12 Modules 25–27: Service, RLS and Performance
- 29.13 Final Assignment: "Blinkit vs Amazon Now – City Performance Report"
30. Interview Questions and Answers 9 concepts
- 30.1 Fundamentals
- 30.2 Data Connectivity and Power Query
- 30.3 Data Modelling
- 30.4 DAX
- 30.5 Visualization, Service and Security
- 30.6 Data Cleaning and Add Column
- 30.7 Folders, Parameters, Databases and Web
- 30.8 Hierarchies, Groups, Drill, Tooltips and Navigation
- 30.9 Visuals, Dynamic Formatting, Maps, Filters and Dashboards
31. Interview Questions Asked in MNC Interviews 11 concepts
- 31.1 How to Answer in Interviews
- 31.2 Deloitte
- 31.3 EY
- 31.4 KPMG
- 31.5 PwC
- 31.6 TCS
- 31.7 Infosys, Wipro and Accenture
- 31.8 Capgemini, Tech Mahindra, HCLTech and LTIMindtree
- 31.9 Genpact, Mu Sigma, Fractal, Amazon and FedEx India
- 31.10 SQL and Excel Questions Paired with Power BI Rounds
- 31.11 Frequently Asked Across MNC Interviews
- 33. Glossary
Appendices
Join an online / offline batch — Enquire now
Ravindra Bagale runs online and offline batches for AWS Cloud, DevOps, Power BI, Excel, Data Analytics, Data Science and Cyber Security.