Phase 04: Dashboards and spreadsheets
Excel, Power BI and Tableau for Analysts: 12 Free Lessons
What Excel, Power BI and Tableau compute underneath, rebuilt and tested, plus how to design for a decision.
What Excel, Power BI and Tableau compute underneath, rebuilt and tested, plus how to design a chart or a dashboard for a decision.
Complete beginner? If you have never run a lesson, start with phase 00, Start here. Lessons 01 to 03 here use SQL from phase 01; lesson 04 uses a little Python and says which parts.
The 12 lessons, in learning order
- 1. Model the data a dashboard needs
How do you turn one flat export of order lines into tables a dashboard can slice by date, product and region without double counting?
Level 2: Intermediate · about 2 hours
- 2. DAX measures from scratch
What does a Power BI measure actually compute, and how do you get the same Total Sales, filtered sales, share, year to date and growth on last year numbers without Power BI?
Level 2: Intermediate · about 2 hours
- 3. Tableau LOD expressions in SQL
How do you compute a number at a different level of detail from the chart it sits in, such as each customer's first order month in a chart by month, or a region's total in a chart by customer?
Level 2: Intermediate · about 2 hours
- 4. Draw a chart from scratch
What does a dashboard tool do when it turns a table into a bar chart, and which chart should you pick in the first place?
Level 1: Beginner · about 2 hours
- 5. Lookups and SUMIFS like Excel
Why does the VLOOKUP total not match finance?
Level 1: Beginner · about 2 hours
- 6. Pivot tables and Power Query steps
Can next month's file be cleaned and merged with one click?
Level 1: Beginner · about 2 hours
- 7. Dashboard design and KPI cards
What should the CEO see in five seconds?
Level 1: Beginner · about 2 hours
- 8. Relationships, role-playing dates and many-to-many
Why does revenue by ship month differ from revenue by order month?
Level 2: Intermediate · about 2 hours
- 9. One metric, one definition: the semantic layer
Why do two dashboards show different active users?
Level 2: Intermediate · about 2 hours
- 10. Slowly changing dimensions
Which region was this customer in when they ordered?
Level 2: Intermediate · about 2 hours
- 11. Row-level security
Can each regional manager see only their own region?
Level 2: Intermediate · about 2 hours
- 12. Tableau table calculations vs LOD
Running total, rank or percent of total: which should be computed where?
Level 2: Intermediate · about 2 hours
What you will be able to do
By the end of the phase you will have built and tested each of these yourself, one per lesson:
- Model the data a dashboard needs: A date dimension with one row per calendar day, including days with no sales, and a weekday column.
- DAX measures from scratch:
SUMas a measure: one total per category. - Tableau LOD expressions in SQL:
FIXEDat customer: each customer's first order month, and new customers per month from it. - Draw a chart from scratch:
nice_max: round the largest value up to a readable axis maximum (50,000, not 38,900). - Lookups and SUMIFS like Excel:
vlookup: exact match (first match wins) and approximate match (largest key at or below), with#N/Aand#REF!. - Pivot tables and Power Query steps: Nine named steps, each a small function that takes a table and returns a new one:
removed_top_rows,promoted_headers,renamed_columns,trimmed_text,capitalized_each_word,filtered_rows,changed_type,removed_duplicatesandadded_custom. - Dashboard design and KPI cards:
period_bounds: this month so far, and the same days of last month. - Relationships, role-playing dates and many-to-many: Revenue by order month, through the date table.
- One metric, one definition: the semantic layer: A model: dimensions and metrics stored as a Python dictionary, with active users defined once.
- Slowly changing dimensions: A Type 1 customer table from a messy change log.
- Row-level security:
visible_sales: the rows one login may see, with deny by default, a wildcard and inactive rows ignored. - Tableau table calculations vs LOD: A running total by month that restarts for each region.
How to start
Clone the course and run the first lesson's test. It fails until your exercise is right. You need only Python 3.9 or later.
git clone https://github.com/Ani19arya/analytics-from-scratch
cd analytics-from-scratch/phases/04-dashboards/01-model-the-data-a-dashboard-needs
python3 test_lesson.pyJobs that ask for these skills
Open roles on Real Analyst Jobs where this phase's skills come up, read daily from company career pages:
