Power BI - Advanced

• An understanding of Power BI
• Experience with Power BI

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction

• Overview of Power BI features and architecture
• Advanced tools and data analysis in Power BI

Getting Started

• Installing Power BI and other components
• Workflow for preparing reports and dashboards
• Overview of the interface

Power Query – importing the data

• Types of connectors
• Query folding
• Extracting data from different sources:
  1. Flat files
  2. Whole folders of data
  3. Websites
  4. Working with unstructured files
• Advanced techniques in Power Query
  1. Merging and appending the tables
  2. Pivoting and Un-Pivoting of the data
  3. Automated columns from examples
  4. Working with inconsistent tables
  5. Tables decomposition
  6. Best practices in analyzing text
  7. Basics of M language

Data Model – why do you need one?

• What is data model?
• Data model normalization
• 2 main types of tables
• Relationship cardinality
  1. One to many
  2. One to one
  3. Many to many
• Filter flow – logic behind the engine
• How to create hierarchy?
• Data modelling best practices

DAX – the best tool to make your date useful

• What is DAX and why is to so good?
• What is a difference between measure and calculated column? Which one should I use?
• Filter context vs. Row Context
• How to create measure table?
• Variables in measures
  1. How to create variables?
  2. Why you should use variables?
  3. How to handle errors and debug with variables?
• Scalar functions
  1. Basic aggregation functions
  2. Iterator functions
  3. SWITCH function – better IF
  4. How you can use COALESCE function?
• Advanced CALCULATE use:
  1. The modifiers
  2. REMOVEFILTERS and KEEPSFILTERS functions with examples
  3. What is a Pattern?
• Table and Filter functions
  1. Most common table functions
  2. Difference and examples of using VALUES and DISTINCT functions
  3. How to incorporate SELECTEDVALUE to your report?
  4. ALLEXCEPT
  5. ALLSELECTED
  6. Why you should now SUMMARIZE?
  7. Different ways to generate new data for your report
• Table joins:
  1. How to CROSSJOIN tables?
  2. EXCEPT and INTERSECT with examples
  3. How you can use UNION function
• Relationships Functions:
  1. What is the difference between physical and virtual relationship?
  2. RELATED and RELATEDTABLE
  3. How to use USERELATIONSHIPS?
• Advanced concept of time intelligence
• How to prepare Fiscal and non-standard calendars for your model?

Advanced visualization techniques:

• How to prepare your own color pallet for your report?
• How to build a theme for all your reports?
• How to create great slicer panel?
• Learn to create compelling graphs that do not lie
• Learn to distinguish between good, bad and wrong visualization
• The simplest recipe to make your report look good
• Interactive Scenario Analysis for your users
• Forecast and trend lines for better data recognition
• Drilling up and drilling down
• Why you should use parameters?
• Bookmarks as your allies in data visualization