Welcome to the fast track to becoming a Power BI practitioner. Each module is two or three hours and can be repeated as needed.
No experince necessary but those with experience will likely still gain useful information.
Requirement: A computer running Power BI Desktop and files (tips).
Power BI Fundamentals I: Data Normalization
In this workshop we analyze a workbook using Power BI Desktop, Power Query and DAX. We normalize the data to transform it to star schema. We also create simple visualizations. We will learn about data modeling and best practices.
- What is Power BI? What is Power Query? What is DAX?
- ETL (Extract Transform Load), Analysis, Visualizations
- Saving and rebinding reports
- Refreshing reports
- Report view, table view, model view, power query
- Star schema
- Loading data using Power Query
- Appending and Merging data using Power Query
- Normalizing data using Power Query
- Creating a measures table and measures
- DAX sum
- Creating interactive visualizations
Power BI Fundamentals II: Star Schema
We analyze a data source that contains many tables. We transform a snowflake schema to a star schema using Power Query. We learn about relationships, de-normalization, primary keys and foreign keys.
- Snowflake vs. Star Schema
- Best practices and vocabulary
- Methodology: ETL, Analysis, Visualizations
- Flattening and merging data using Power Query
- Creating measures tables and measures
- Relationships
- Creating a calendar table
- DAX examples, sum, sumx, average, countrows
Power BI Fundamentals III: Time Intelligence
We learn best practices for time intelligence. We will create calendar/date tables, compare dates, aggregate by time periods. We will also cover government fiscal periods and NGO fiscal periods such as years starting April 1st and July 1st start. We explore DAX and the filter context.
- Date/Calendar tables versus built in time intelligence
- Fiscal year starting Jan 1st versus April 1st versus July 1st.
- Speed of Service, Service Level, Groups, calculations
Power BI Fundamentals IV: Visuals
- Location, Maps
- Hierarchies
- Slicer magic
- Publishing reports
- Selection Pane
- Filter Pane
- Wireframes
- The color white
Power BI Fundamentals V: Tracking Budgets
This workshop is a walkthrough of how to create a basic government style budget, tracking sheets of expenses and roll it into a progress report.
It uses April 1st fiscal year calendar. The same principals apply to a July 1st fiscal year start as well.
The concepts used are, Append, Merge, Unpivot, Time Intelligence Calendars, Basic DAX and basic visuals.
- Merge and Append
- Creating appropriate dimension tables
- Creating relationships
- Fiscal Calendar table
- Basic Measures
- Basic Visualiztions
Power BI DAX Party
We’ll explore workload, caseload, compare dates, latency, measures and histograms.
- Open cases, work in progress, pending orders calculations
- if function, if statement, conditional columns
- Switch function
- Measures, calculated columns, custom columns
- Calculate, variables, percent of total
- Techniques for typing DAX
- AverageX, Average, SumX, Sum, Distinct Count, CountRows
Power BI ETL Magic
Tips and tricks for Power Query. Binding to folder, loading many sheets, unpivot, web.
- Cross tabs, pivot versus unpivot
- Loading N sheets within workbook
- Loading N workbooks within folder
- Loading CSVs
- Loading direct from web or intranet
Power BI M Language
In depth look at Power Query. Mashup programming language, ETL mastery.
- Editing queries
- Naming and commenting steps
- Various practical examples
Power BI Multiple Fact Tables
Multiple Fact Tables, Header Detail Pattern, Allocation. We look at dealing with fact tables of different granularity.
- Forecasting
- Header Detail problem and Allocation
- Allocating freight by transaction count, item count and weight
Excel BI
We will review Power BI Fundamentals I, II and III but we will show the same techniques within Excel. This is useful if your organization has not adopted Power BI or you prefer Excel. Also for those who want to review best practices.
We will cover how to pivot and analyse multiple tables and multiples sources. How to make charts and filter results.
- Power Query
- Power Pivot
- Data Model
- DAX
- Advantages of DAX over Excel formulas
- Merge vs. VLookup
- Measures Tables
- Calendar Tables
- Best practices
Lookup Hookup
Excel VLookup, Index, Match, XLookup
Power Query Merge, Left Outer Join, Right Outer Join, Inner Join, Full Outer Join, Left Anti Join, Right Anti Join
DAX Related, LookupValue, RelatedTable