top of page

AGENDA

The following sessions will be covered over the two days   

8:30 Registration and Welcome Coffee    
9:00 Course Starts    
10:30 - 11:00 Morning Tea    
12:30 – 1:30 Networking Lunch    
3:00 – 3:30 Afternoon Break    
5:00 Course Ends 

Part One:
PowerQuery & AI 

  • Review Power Query basics. 

  • Power Query Practice – Messy Data. 

  • Hands-on Challenge: Dealing with Errors and Nulls. 

  • Query folding and Data Modeling. 

  • Merging Queries. 

  • Avoiding Many to Many Relationships using Bridging Tables: two different methods. 

Part Three:
Data Analysis,
Data Modelling, and Putting Learnings into Practice 

  • Gain tips for effective data modeling and performance optimization. 

  • Automate tasks using Power Query functions. 

  • Enhance your reports using Smart Narratives, text box fields, and field parameters. 

  • Analyse multiple date fields in one report with the USERELATIONSHIP function. 

  • Add advanced features like Tooltips, Drill Throughs, Buttons, and Bookmarks. 

  • Work through a live, interactive demo using ChatGPT with natural language queries to build a data model. 

Part Two:
DAX & AI 

  • Recap on DAX basics and filter context. 

  • Introduction to DAX variables. 

  • Efficient calculations allowing for multiple IFs using SWITCH and TRUE. 

  • Explore the AI features that are built into Power BI. 

  • How to leverage AI and ChatGPT for efficient DAX coding, complete with live examples. 

  • Creating Calculated Columns and Data Modeling. 

  • How to use and apply the Iterator Functions: like SUMX. 

  • Calculation %s of Sub Totals. 

  • DAX Functions Overview: EXCEPT, ALL, CONCATENATEX, ISBLANK. 

  • Date-Based Calculations. 

  • Functions: TOTALYTD, MAX, DATEADD, DATESINPERIOD, DATESBETWEEN. 

  • Uses for and applications of Disconnected Tables. 

  • How to use Field Parameters to add flexibility and functionality to your reports. 

Part Four:
Wrap-up & Next Steps 

  • Summarize the key takeaways and gain access to additional resources for continued learning. 

Interactive Exercises: 

  • Learn powerful prompts in ChatGPT for writing and documenting Power Query code. 

  • Power Query Referencing Techniques. 

  • Reference a cell in relation to a cell on another row. 

  • Change a cell value based on row position. 

  • Referencing earlier steps. 

  • Dynamically locating the number of rows to remove. 

  • Hands-on Challenge: Dealing with Double Headings using Transpose and Pivot. 

  • Introduction to Dataflows. 

  • Power Query Anti Joins to flag data mismatches. 

  • Adding a last refresh date. 

Vote

ask the experts

Apply the skills you’ve learned to a sales database to import the data, transform it, determine the relationships required, build the data model, and develop the measures required to achieve some specified objectives. Our experienced instructor will be on hand to give tips and advice to help when you need it.

Comprehensive course notes, exercises & solutions are included.

As this training is structured, educational and technical in content, and enhances your ability to do your job, this training can count towards CPD hours. Please contact your CPD professional body directly for more details.
bottom of page