Power BI + Power Query + SQL
Complete Course Content
Trainer: Veera Mahesh
Power BI + Power Query + SQL - Complete Course Content
Part 1: SQL for Power BI
Module 1: SQL Basics
- Objective: Build strong foundational SQL skills for Power BI data modeling and analysis.
- Introduction to SQL
- Database concepts and relational model
- SQL Server Management Studio / SQLite setup
- Data Retrieval
- SELECT, DISTINCT, and column aliasing
- Sorting data using ORDER BY
- Filtering Data
- Using WHERE, AND, OR, BETWEEN, IN, LIKE, and IS NULL
- Understanding filter precedence
- Joins
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
- Joining multiple tables
- Practical exercises with real-world business data
- Common Table Expressions (CTE)
- Understanding CTE syntax
- Recursive CTE examples
- Use cases in Power BI query folding
Part 2: Power Query (Power BI)
Module 1: Introduction to Power Query
- Overview of Power Query and its role in the Power BI ecosystem
- Power Query Editor interface overview
- Applied Steps and Query Settings
- Enabling Formula Bar and understanding step-by-step transformations
- Difference between Power Query and DAX
- Importing data from multiple sources: Excel, CSV, Folder, SQL Server, Web, SharePoint
- Data type detection and changing data types
Trainer: Veera Mahesh | Page 2
Power BI + Power Query + SQL - Complete Course Content
Module 2: Data Transformation Basics
- Rename, remove, reorder columns
- Filtering rows (Keep / Remove rows)
- Removing duplicates and blanks
- Replacing values and handling missing data
- Splitting columns (by delimiter, number of characters, position)
- Merging columns
- Changing text case (Upper, Lower, Proper)
- Fill down and fill up operations
- Pivoting and unpivoting columns
- Transposing tables
Module 3: Data Merging and Appending
- Understanding relationships between datasets
- Merge Queries (Join operations): Left, Right, Inner, Full Outer, Anti Join
- Append Queries (Union operations)
- Combining files from a folder dynamically
- Handling schema mismatches during append
- Using Combine Binaries and Sample File in folder connections
Module 4: Advanced Data Transformations
- Group By and Aggregate functions
- Adding Conditional Columns (if-then-else logic)
- Creating Custom Columns (manual M expressions)
- Date transformations: Extract Year, Month, Day, Week; Add custom fiscal year and quarter
- Time transformations (hour, minute, second)
- Extract text (before/after delimiter, substring, length)
- Replace errors and remove errors
- Detecting nulls and handling missing data
- Adding Index columns and Rank logic
Part 3: Power BI Desktop
Module 1: Introduction to Power BI
Trainer: Veera Mahesh | Page 3
Power BI + Power Query + SQL - Complete Course Content
- Overview of Power BI ecosystem
- Components: Power BI Desktop, Service, Mobile, Gateway
- Power BI architecture (Data, Model, Visualization layers)
- Power BI file types (.pbix, .pbit, .pbids)
- Import Mode vs DirectQuery vs Live Connection
Module 2: Power BI Interface Overview
- Home, View, and Modeling ribbons
- Report, Data, and Model views
- Filters, Visualizations, and Fields panes
- Q&A and Insights features
- Managing relationships and navigation
Module 3: Data Sources and Connections
- Connecting to Excel, CSV, Folder, Web, SQL Server, SharePoint
- Connecting to APIs and cloud sources (Azure Databricks, Dataverse, SAP S4HANA)
- Using parameters in connections
- Data refresh options (manual, scheduled, incremental refresh)
Module 4: Power Query (Data Transformation)
- Clean, shape, and transform data using Power Query Editor
- Merge, Append, Pivot, and Unpivot operations
- Create custom and conditional columns
- Group By, Replace Values, Remove Duplicates
- M language basics and query dependencies
Module 5: Data Modeling
- Star schema and snowflake schema concepts
- Dimension and fact tables
- Creating relationships (one-to-many, many-to-one, one-to-one)
- Active vs inactive relationships
- Relationship cardinality and cross-filter direction
- Auto-detect and manage relationships
- Hide, sort, and organize fields in the model view
- Modeling best practices and optimization
Trainer: Veera Mahesh | Page 4
Power BI + Power Query + SQL - Complete Course Content
Module 6: DAX (Data Analysis Expressions)
- Calculated Columns vs Measures
- Row context vs Filter context
- Basic DAX functions: SUM, COUNT, DISTINCTCOUNT, AVERAGE
- Logical functions: IF, SWITCH, AND, OR
- Text functions: CONCATENATE, LEFT, RIGHT, FORMAT
- Date functions: YEAR, MONTH, TODAY, DATEDIFF, SAMEPERIODLASTYEAR
- Time intelligence: TOTALYTD, DATEADD, PREVIOUSMONTH
- FILTER, CALCULATE, ALL, ALLEXCEPT, REMOVEFILTERS
- Ranking and percent calculations (RANKX, PERCENTILE)
- Variables in DAX (VAR RETURN)
- Error handling functions (IFERROR, COALESCE)
- DAX performance best practices
Module 7: Visualization Concepts
- Visual types: Table, Matrix, Cards, KPIs, Charts
- Custom visuals from marketplace
- Conditional formatting in visuals
- Tooltips, drill-down, drill-through
- Bookmarks and buttons (navigation, reset filters)
- Hierarchies (Date, Geography, Category)
- Small multiples and combo charts
- Sync Slicers and slicer interactions
- Field parameters for dynamic visuals
- Report page tooltips and report themes
- Customizing visuals with formatting pane
- Export and analyze in Excel
Module 8: Filters and Interactions
- Visual-level, Page-level, and Report-level filters
- Slicers and cross-filter behavior
- Edit interactions between visuals
- Drill-through filters
- Top N filtering and relative date filtering
- Using measure-based filters
Trainer: Veera Mahesh | Page 5
Power BI + Power Query + SQL - Complete Course Content
Module 9: Advanced Modeling and Features
- Role-playing dimensions (e.g., multiple date fields)
- What-if parameters
- Calculation groups (using Tabular Editor)
- Dynamic titles and labels using DAX
- KPI indicators and target-based visuals
- Field parameters (dynamic columns and measures)
- Composite models (Import + DirectQuery)
- Aggregations in Power BI
- AI visuals (Key Influencers, Decomposition Tree, Smart Narrative)
Module 10: Performance Optimization
- Data model size reduction techniques
- Query folding and performance monitoring
- Optimize relationships and remove redundant columns
- Manage auto date/time hierarchy
- Model view performance analyzer
- Import vs DirectQuery vs Hybrid performance trade-offs
- Aggregation tables
- Compression and cardinality awareness
Module 11: Security
- Row-Level Security (RLS)
- Dynamic RLS using DAX and user principal
- Role creation and testing security
- Object-level security (OLS)
- Data masking and restricting visuals
Module 12: Publishing and Sharing
- Publishing reports to Power BI Service
- Managing workspaces and apps
- Report sharing options
- Schedule refresh and data gateway setup
- Export options: PDF, PowerPoint, Excel
- Embedding Power BI reports in apps/websites
Trainer: Veera Mahesh | Page 6
Power BI + Power Query + SQL - Complete Course Content
- Version control with Power BI Template files
Module 13: Power BI Service Add-ons
- Dataflows overview
- Datamarts and semantic models
- Metrics (Goals) in Power BI Service
- Power BI Copilot (AI assist in visuals & DAX)
- Paginated Reports integration
- Power BI Mobile view and layout
Module 14: Best Practices
- Folder and file organization
- Naming conventions (tables, columns, measures)
- Using measure tables
- Data model documentation
- Reusable templates and parameters
- Governance and workspace structure
Trainer: Veera Mahesh | Page 7