NOTE: This is PART 1 of a 2-Part Microsoft Power BI series:
-
Up & Running with Power BI Desktop
-
Publishing to Power BI Service
Don’t forget to complete PART 2 to learn Power BI Service and take your Power BI skills to the cloud!
__________
Hear why this is one of the TOP-RATED Power BI courses on Udemy:
“Instructor is top notch – moves at the right pace and keeps it interesting. Best Power BI course on Udemy!”
-Adam Edwards
“Resources are awesome. Presenter is brilliant. I found this course more useful than the offical Power BI course from Microsoft. Things are easy to follow, and presentations are of high quality.”
-Jacobus M.
“Chris is a skilled communicator and does a great job of explaining a complex tool like Microsoft Power BI. His ‘pro-tips’ are great for new user productivity and gaining a sense of the big picture, and I value his best practices on building and managing Power BI queries and reports. I’m feeling much more confident to dig in and use Power BI on my own projects!”
-Bill Jerrow
__________
COURSE DESCRIPTION:
If you’re looking for a comprehensive, hands-on guide to learning Microsoft Power BI Desktop, you’ve come to the right place.
Power BI is quickly becoming the world’s most powerful self-service business intelligence platform, and an absolutely essential tool for data professionals and beginners alike. With Power BI you can connect to hundreds of data sources, build complex relational models using simple and intuitive tools, and design stunning, interactive dashboards from scratch — all for free.
__________
THE COURSE PROJECT:
In this course, you’ll be playing the role of Lead Business Intelligence Analyst for Adventure Works Cycles, a global manufacturing company. Your mission? To design and deliver a professional-quality, end-to-end business intelligence solution, armed only with Power BI and a handful of raw csv files.
But don’t worry, I’ll be guiding you through the ins-and-outs of Power BI Desktop, sharing crystal clear explanations and helpful pro tips each step of the way. We’ll follow a steady, systematic progression through the Power BI workflow, and break down our project into FOUR KEY OBJECTIVES:
__________
POWER BI OBJECTIVE #1: Connect & Transform the Raw Data
-
Intro to the Power BI Query Editor
-
Types of Power BI Data Connectors
-
Basic Table Transformations
-
Text, Number & Date Tools
-
Index & Conditional Columns
-
Grouping & Aggregating Data
-
Pivoting & Unpivoting
-
Modifying, Merging & Appending Queries
-
Connecting to Folders
-
Defining Hierarchies & Categories
-
Query Editing & Power BI Best Practices
__________
POWER BI OBJECTIVE #2: Build a Relational Data Model
-
Intro to Database Normalization
-
Data (“Fact”) Tables vs. Lookup (“Dimension”) Tables
-
Creating Power BI Table Relationships
-
“Star” vs. “Snowflake” Schemas
-
Active vs. Inactive Relationships
-
Relationship Cardinality
-
Connecting Multiple Data Tables
-
Filtering & Cross-Filtering
-
Hiding Fields from the Power BI Report View
-
Data Modeling & Power BI best Practices
__________
POWER BI OBJECTIVE #3: Add Calculated Fields with DAX
-
Intro to Data Analysis Expressions (DAX)
-
Calculated Columns vs. Measures
-
Row Context vs. Filter Context in Power BI
-
DAX Syntax & Operators
-
Common Power BI Functions
-
Basic Date & Time Formulas
-
Logical & Conditional Statements
-
Text, Math & Stats Functions
-
Joining Data with RELATED
-
CALCULATE, ALL & FILTER Functions
-
DAX Iterators (SUMX, AVERAGEX)
-
Time Intelligence Formulas
-
DAX & Power BI Best Practices
__________
POWER BI OBJECTIVE #4: Design Interactive Power BI Reports
-
Intro to the Power BI Report View
-
Adding Basic Charts to Power BI Reports
-
Formatting & Filtering Options
-
Matrix Visuals
-
Slicers & Timelines
-
Cards & KPIs
-
Power BI Map Visuals (Basic, Fill, ArcGIS)
-
Treemaps, Lines, Areas & Gauges
-
Editing Report interactions
-
Adding Drillthrough Filters
-
Linking to Report Bookmarks
-
Using “What-If” Parameters
-
Managing & Viewing Roles
-
PREVIEW: Publishing to Power BI Service
-
Power BI Data Viz Best Practices
__________
By the end of the Adventure Works project, not only will you have developed an entire business intelligence tool from the ground up using Power BI, but you will have gained the knowledge and confidence to apply these same concepts to your own Power BI projects.
For those looking for more opportunities to test their Power BI skills, I’ve also included an additional set of project files for a bonus Final Project. This is your chance to showcase all of the skills you’ve developed throughout the course, and apply them to a brand new data set from Maven Market, a global supermarket chain.
Whether you’re a casual Power BI user, aspiring analyst, or data science professional, this course will give you the tools you need to become an absolute Power BI ROCK STAR — guaranteed.
__________
Join today and get immediate, lifetime access to the following:
-
100+ page Power BI ebook
-
Downloadable Power BI project files
-
Homework exercises & quizzes
-
1-on-1 expert support
-
Course Q&A forum
-
30-day money-back guarantee
See you in there!
-Chris (Founder, Maven Analytics)
__________
P.S. Looking to master the full Microsoft Excel, Power BI + SQL stack? Search for “Maven Analytics” and complete the courses below to become a business intelligence ROCKSTAR:
-
Excel Pro Tips for Power Users
-
Advanced Excel Formulas & Functions
-
Data Visualization with Excel Charts & Graphs
-
Data Analysis with Excel PivotTables
-
Excel Power Query, Power Pivot & DAX
-
Up & Running with Power BI Desktop
-
Publishing to Power BI Service
-
SQL Database Analysis for Beginners
-
Advanced MySQL for Analytics & Business Intelligence
Getting Started
Walkthrough to cover exactly how the course is structured and what you need to know before diving into Microsoft Power BI Desktop.
In this lecture, I'll introduce the course project, where you've just been hired to deliver and end-to-end BI solution, and provide a sneak preview of the final project.
In the last Getting Started section, I'll cover exactly what to expect (and what NOT to expect) from this course.
Introducing Microsoft Power BI Desktop
Meet Microsoft Power BI: one of the industry's top self-service business intelligence platforms. In this lecture we'll discuss the key benefits that make this such a game changing product and discuss the ways Power BI and Power Excel are similar and different.
In this section we'll cover how to download Power BI Desktop and configure options and regional settings to make sure you're working with the same Power BI setup.
In this lecture we'll introduce the three core views within Power BI Desktop, the Report view, the Data view, and the Relationship view, and I'll cover how these core views fit into the typical business intelligence workflow.
In this section I'll discuss some of the resources that I find most helpful, like the "Help" section in Power BI Desktop, the Microsoft Power BI blog, and the Microsoft Power BI YouTube channel.
In this lecture we'll introduce the new Power BI Desktop ribbon interface, introduced as a preview feature in November 2019 and rolled out to general availability in early 2020.
Connecting & Shaping Data with Power BI Desktop
In this lecture we'll kick things off and cover the robust data connection library where you can connect to just about any data source. I'll highlight some of the available connectors and walk through the Get Data dialogue box.
The Query Editor is your cockpit for loading and transforming raw data in Power BI Desktop. In this lecture we'll take a tour of the tools and features that will help us transform and shape our data.
In this lecture we'll learn about some of the most important table transformation tools and options in Power BI Desktop, including promote header row, duplicate, move, and rename columns, and keep or remove rows.
In this lecture we'll cover Power BI Desktop tools designed specifically to work with text, including split column, format column, or trim and clean.
In this lecture we'll review Power BI Desktop tools designed specifically to work with numbers or data fields, such as returning aggregated values like sums or averages, creating new calculated columns, rounding numbers, etc.
In this lecture we'll cover Power BI Desktop tools specifically designed to work with date and time fields, like calculating months, weeks, weekdays, quarters, etc.
In this lecture I'll quickly demonstrate how you can use custom M queries to build a rolling calendar in Power BI Desktop that will always update with dates through the current day.
This lecture will show you how to create new unique identifiers using index columns in Power BI, as well as calculated fields based on custom user-defined conditions.
In this lecture we'll practice using "Group By" tools in the Transform tab of the Query Editor to aggregate or roll up raw data to new levels of granularity.
This lecture demystifies the concept of "pivoting" or "unpivoting" a data table. I'll show you exactly what these tools do, and demonstrate with a sample table in Power BI.
This lecture demonstrates how to merge queries together within Power BI Desktop to pull fields from one table into another based on common fields or "keys".
In this lecture, I'll show you how to append or "stack" data from multiple Power BI queries that share the same column structure and data types.
In this lecture, I'll show you a more efficient and scalable method to append or "stack" data from multiple Power BI data sources sotred within a single folder.
The Data Source Settings in the Query Editor allows you to manage data source connections and permissions.
In this lecture, we'll cover how to customize your Query Refresh settings as well as refresh all queries.
In this lecture, we'll explore the Power BI "Modeling" tab in the Data view, where I'll show you how to edit field properties to define specific categories.
In this lecture we'll practice creating hierarchies in our Power BI model, which are groups of nested columns that reflect multiple levels of granularity (such as country, state, and city).
In the PRO TIP lecture, I'll show you how to import a fully built data model from Excel into Power BI Desktop.
In this lecture, I'll share some of the best practices, like organizing your datasets and establishing a folder/file structure, when connecting and shaping data in Power BI Desktop.
Creating Table Relationships & Data Models in Power BI
A data model is a group of tables that are connect by a common field or "key". In this lecture, I'll explain the core concept of joining Power BI datasets together with common keys.
This lecture covers one of the most important topics in the course: database normalization. Normalization is all about structuring tables in a way where each table serves a distinct and specific purpose.
In this lecture we'll compare and contrast the two primary types of tables in a Power BI model: data (or "fact") tables and lookup (or "dimension") tables.
In this lecture I'll explain the difference between manually merging fields from multiple tables and creating relationships to connect them using the Power BI data model (which is much more efficient!)
In this lecture we'll use Power BI Desktop to create our first table relationships.
In this lecture I'll cover "Snowflake" schemas, which are essentially dimension tables which are "chained" together through common keys.
The Manage & Edit Relationships dialogue allows you to edit, add, or delete table relationships within Power BI Desktop.
In this lecture I'll demonstrate how to create multiple relationships against the same key, and how to determine which relationship is active vs. inactive in Power BI Desktop.
This lecture explores the concept of cardinality, and demonstrates the differences between 1-to-1, 1-to-many, and many-to-many relationships. I'll also show you exactly why 1-to-many relationships are critical when it comes to building normalized data models in Power BI Desktop.
In this lecture I'll demonstrate exactly how to build a Power BI model containing multiple data tables. Rather than connect those tables together, we'll connect them indirectly via relationships to shared lookup tables.
This lecture demonstrates the importance of filter direction within a Power BI data model, and explains the concept of filter context flowing "downstream" to related tables.
In this lecture I'll cover examples of how, and when, to use two-way filters (bi-directional filtering) in Power BI Desktop. This technique allows filter context to flow from dimension to fact tables and from dimension to fact tables.
In this lecture, I'll cover how, and when, to hide fields from your Power BI Report view.
In this lesson I'll walk you through the updated "Model" view in Power BI Desktop, which includes tools to quickly edit model properties, organize fields, and create custom views based on subsets of your full data model.
In this lecture I'll outline my personal favorite Power BI data modeling best practices.
Analyzing Data with DAX Calculations in Power BI
Meet DAX, or Data Analysis Expressions, the formula language that drives Power BI.
In this lecture I'll introduce the first method of using DAX to create new calculated fields: calculated columns. I'll showcase some "good" and "bad" examples, and demonstrate how they can be created within the data model window.
In this lecture I'll introduce the second method of using DAX to create new calculated values: measures. I'll explain how they can be used and why they are so powerful in Power BI Desktop.
In this lecture, I'll create a little "cheat-sheet" by recapping the difference between Calculated Columns and Measures and outline how and when they're used.
In this lecture, I'll cover two options for adding columns and measures in Power BI, and highlight the important differences between the two options.
In this lecture, I'll cover the difference between implicit measures, which are measures that are automatically created by Power BI Desktop when you drag raw numerical fields into the visuals pane, and explicit measures, which are created by entering DAX functions.
In this lecture I'll cover filter context, which allows us to understand exactly how each "cell" is being calculated, and how it impacts DAX measures.
In this lecture, I'll guide you through the actual steps that Power BI Desktop takes behind the scenes to calculate each DAX measure. This demonstration will be critical to troubleshooting calculation errors and understanding precisely how measures work within Power BI.
In this lecture I'll walk through DAX formula syntax and outline the most common types of operators.
In this lecture I'll outline some of the most common DAX categories in Power BI (Math & Stats, Logical, Text, Filter, and Date & Time), as well as example functions.
In this lecture, I'll cover how to add calculated columns with basic date and time functions like WEEKDAY, TODAY, and DATEDIFF.
In this lecture I'll introduce and demonstrate several common logical functions in DAX, including IF, IFERROR, AND, OR, etc.
In this lecture I'll introduce and demonstrate a number of text-specific functions in DAX, including LEN, CONCATENATE, UPPER/LOWER, LEFT/MID/RIGHT, SEARCH, and SUBSTITUTE.
In this lecture I'll show you how to use the RELATED function to create new calculated columns that retrieve values from related tables in the Power BI data model.
In this lecture I'll introduce and demonstrate several common math and statistics functions in DAX, such as SUM, DIVIDE, MAX, MIN, and AVERAGE.
In this lecture I'll demonstrate how to use a variety of COUNT functions in DAX, including COUNT, COUNTA, COUNTROWS, and DISTINCTCOUNT.
In this lecture, I'll discuss an important characteristic of our sample transaction data, and discuss how it impacts our reporting views.
In this lecture I'll introduce arguably the most powerful DAX function of all: CALCULATE. I'll explain exactly how this function can be used, and demonstrate several examples within Power BI Desktop.
In this lecture I'll explain how to use the ALL function to remove filter context, and demonstrate how it is commonly used for "% of whole" calculations defined by DAX measures.
In this lecture I'll explain how to use the FILTER function to return a table that represents a subset of another table, and demonstrate how FILTER can be used within the CALCULATE function.
In this lecture I'll introduce you to iterator, or "X" functions, which operate by repeating a calculation across all rows in a table and aggregating the results. In this demo we'll look at the SUMX function specifically.
In this lecture we'll introduce DAX's powerful time intelligence formulas, and demonstrate how to use them to measure performance-to-date, make period-over-period comparisons, and calculate running totals and moving averages.
In this lecture I'll outline some of my personal favorite DAX best practices.
Visualizing Data with Power BI Reports
In this lecture we'll discuss the panes, toolbars, and options available within the Power BI Desktop "Report" view.
In this lesson, we'll review the basic ways to add objects and charts into the Power BI Report view, via the Home tab or visualizations pane, and kickoff building our Adventure Works report.
In this lecture I'll walk through adding our first chart to the Power BI Report canvas (a stacked bar chart), and cover the fields and formats options available for this specific chart type.
In this lecture I'll walk through conditional formatting options in the newer versions of Power BI Desktop (post Nov 2018)
In this lecture, I'll cover Power BI report formatting options, which is all about customizing the look and feel of the visualizations, and highlight formatting options for Line & Column Chart, Matrix, and Donut Chart types.
I'll cover the incredibly powerfull filtering options within Power BI and outline the four primary filter types: Visual Level, Page Level, Report Level, and Drillthrough.
In this demo, I'll focus on the Matrix visualization type and customize the visual to make it easily interpretable.
In this demo, we'll create a Date Slicer and use it to filter the views in our Power BI report.
In this demo I'll cover the three standard card types in Power BI: single row card, multi-row card, and KPI card. Cards are a great way to draw attention to important values and KPIs.
In this demo I'll cover how card visuals can be used for text-based fields (not just values) in Power BI, and how to format cards in a clear and effective way.
In this lesson, we'll introduce Power BI Desktop's powerful, flexible, and user-friendly mapping tools.