Chandoo
How to Make a Sales Dashboard in Power BI - FREE Live Masterclass
updated
Topics covered in this video:
0:00 - What is Power BI Service
0:55 - Accessing Power BI Service via app.powerbi.com
2:08 - Power BI Service vs. Power BI Desktop
2:31 - Opening a report in Power BI Service
2:55 - Using a Report in Power BI Service
3:40 - Resetting filters in Power BI
4:00 - Multi-selecting items in Power BI
5:00 - Using "Filter Panel" in Power BI Service
6:17 - Sorting visuals
6:52 - Focus Mode
8:15 - Copying a chart or visual to Word / Outlook / Teams
8:54 - Accessing "other pages" of your reports
9:08 - Personal Bookmarks
10:10 - Exporting Power BI Data to Excel
11:06 - Saving Power BI Report as a PDF
11:52 - Presenting Power BI Reports in Power Point (and teams)
13:00 - Using other buttons in the Power BI Service (app.powerbi.com)
13:50 - Saving Favourites (working faster)
15:00 - Collaboration with team members using Power BI Service
15:38 - Closing Remarks on Power BI Service
Demo Report:
app.powerbi.com/view?r=eyJrIjoiOTI5YjM5ZmQtMTdlOS00YWZlLWFiYWUtMTg0MTM2NDU1OTQ4IiwidCI6IjUzNTA4ZDUyLWQxYjAtNDliMC1iNGJhLTM1MzNjMTI0OWEwMSJ9
Note: You won't be able to see the filter panel or other buttons / options. For that you need to use your own tenant's Power BI service.
Learn more:
1️⃣ Getting Started with Power BI Desktop (10 mins) - youtu.be/9tF1IrfLflg
2️⃣ Getting Started with Power BI Desktop (30 mins) - youtu.be/al4OfLIOl2w
3️⃣ Power BI End to End Masterclass (1 hour) - youtu.be/uwe8C7K8fXY
🧑💻 Power BI Full Course - chandoo.org/wp/power-bi-course
~
#powerbi
B = Learn about the business
L = View new job as an opportunity to LEARN new things
S = Start small
+
bonus A = Use AI to get a head start
#newjob #careeradvice
Sample file: chandoo.org/wp/wp-content/uploads/2025/08/dense-rank-excel.xlsx
More Excel formulas: youtube.com/playlist?list=PLmejDGrsgFyDUK8Q_CfZEtQvmRqV0UMm_
#excel #sql #rank
In this example, I share how I am using self-joins to get the "prior weekly rental" and "prior move out date" from rental agreement data with Power Query.
Sample data file: chandoo.org/wp/wp-content/uploads/2025/08/self-join-example.xlsx
Need Power BI Skills for yourself?
Join my Power BI course - chandoo.org/wp/power-bi-course
~
A data engineer got divorced. When friends ask her what happened, she replied,
"I left the join because it wasn't the right join." 😆
#powerbi #powerquery
~
XLOOKUP is one of the most powerful and useful XL functions ever. In this video, learn how to take your XLOOKUP skills to next level and impress everyone with a sneaky little trick, by looking up 1.
Topics covered:
✔️ How to do multi-criteria lookups
✔️ Boolean arithmetic - the powerful of 1s & 0s
✔️ Advanced AND criteria
✔️ Advanced OR criteria with xlookup
✔️ How to see all matching values? FILTER(1) trick!
📁Sample file - chandoo.org/wp/wp-content/uploads/2025/07/xlookup-multiple-criteria.xlsx
📺More on XLOOKUP
XLOOKUP in SIX (6) Minutes - youtu.be/dlBuWllegRg
I stopped using VLOOKUP - youtu.be/DDlZXXEI1bU
XLOOKUP - 10 Advanced Tricks - youtu.be/YyhJe5tMq58
🧑💻 Complete Advance Excel Course:
Learn Advanced Excel and become awesome at work. Join my Excel School program today and master vital spreadsheet skills - right from Power Query to Dashboards. Everything in one place with 50+ practice files, 30 hours of video content and 100+ lessons.
Join here - chandoo.org/wp/excel-school-program
~
#xlookup #excel
Be exciting. Power BI is pretty powerful, but the default charts look 🤮. In this video, learn how to make the ultimate column chart to impress the stakeholders.
📁 Sample Data (use it to build the chart) - chandoo.org/wp/wp-content/uploads/2025/07/quick-tips-sampledata.xlsx
~
#powerbi #datavisualization
"XLOOKUP is probably the most powerful XL function" - my grandma
How to use XLOOKUP?
=XLOOKUP(
value you want to find,
in the column where your data is,
specify the column you want as a result)
XLOOKUP Example 1:
=XLOOKUP(
"Richard",
B8:B42,
H8:H42)
XLOOKUP Example 2:
=XLOOKUP(
"EMP1012",
C8:C42,
B8:B42,
"Person not found")
Why use XLOOKUP?
XLOOKUP is an easy, versatile and powerful XL function that let's you search what you want in your data and get matching results. It is better version of VLOOKUP and INDEX+MATCH functions.
In this 6 minute video, learn how to use XLOOKUP in Microsoft Excel.
The video explains:
✅ How to use XLOOKUP?
✅What happens when XLOOKUP can't find the value?
✅XLOOKUP and returning the entire row
✅XLOOKUP with two sheets
More Excel XLOOKUP examples and explanation:
Read my detailed XLOOKUP article for full information - chandoo.org/wp/xlookup-examples
XLOOKUP Sample file - chandoo.org/wp/wp-content/uploads/2025/07/xlookup-howto.xlsx
XLOOKUP Advanced Techniques (10 powerful examples) - youtu.be/YyhJe5tMq58
I stopped using VLOOKUP, here is why - youtu.be/DDlZXXEI1bU
~
=XLOOKUP("best day ever", days, you)
#xlookup #excel
~
Power BI is great for generating HR reports, employee reports and dashboards. In this video, learn how to use Power BI, Power Query, Power Pivot, DAX, SVG Measures and visual design to create an impressive employee attendance dashboard.
📁Get the files:
Sample data: github.com/chandoo-org/Power-BI/blob/main/SVG/emp_attendance_data.xlsx
Demo report: github.com/chandoo-org/Power-BI/blob/main/SVG/attendance%20dashboard%20-%20explained.pbix
Watch Part1 video - youtu.be/djUyi4oOAEM
🕑 Video Topics & Timestamps:
0:00 - Demo of our Employee Power BI Dashboard
1:29 - A closer look at the data
2:54 - Dashboard Design - Inspiration
3:43 - Designing the Dashboard in OneNote
7:48 - Loading and transforming data (with Power Query)
16:00 - Data Modelling
17:28 - Background Design (in PPT)
20:50 - Page Design in Power BI
21:40 - What visuals we need?
22:23 - Setting up a dedicated measures table in Power BI
23:02 - DAX Measures for our analysis
31:03 - Designing the Calendar View (with just matrix)
33:00 - Using SVG to create a better calendar view
41:35 - Adjusting the SVG Calendar Colors and Matrix Format
45:55 - Designing Employee Card Visual with SVG + DAX
49:00 - But images are not loading with SVG - fixing the issue with Power Query
56:00 - Formatting the dashboard
1:01:00 - Adding dynamic dashboard title to tell a story
1:03:00 - In conclusion
📚 Additional Resources:
Watch Part1 video - youtu.be/djUyi4oOAEM
Dashboard inspiration by Seun Adeyemo public.tableau.com/app/profile/bigxpt/viz/SALARYWORKHOURS/SALARY
Extracting large image bytes with PQ - blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets
SVB DAX Basics - chandoo.org/wp/how-to-svg-dax-measures-power-bi
Kerry Kolosko's website for SVG inspiration - kerrykolosko.com/portfolio
Boxy SVG Editor - boxy-svg.com/app
📺 More Power BI Dashboards & Reports:
HR Dashboard with Power BI - youtu.be/5KaIU-9EF-0
Sales Dashboard with Power BI (basic) - youtube.com/live/mXD0Peot4hY
Sales Dashboard with Power BI (adv) - youtu.be/ooJO7NW4uJU
Finance Dashboard with Power BI - youtu.be/i3AR0gt9SHA
Insurance Dashboard with Power BI - youtube.com/live/TazNFzgWtgA?feature=share
Hotel Dashboard with Power BI - youtube.com/live/qjzjYurmb5s?feature=share
💥Master Power BI for data analysis with my course 👉 chandoo.org/wp/power-bi-course
~
#powerbi #hrdashboard #dax
Ever wondered how an experienced Data Analyst actually uses AI? In this video, let me lift the curtains and showcase my exact process. You will be
surprised for sure.
❌ What I don't use AI for?
✅ What I use AI for?
🧪 To generate Test data and samples
🎨 Design & Mock-up stages of projects
🧑💻 Writing Python Scripts
🏃♀️➡️ Getting a head start on new projects
✍️ Documenting my work
📗 My Prompting Cheat sheet with 15 prompts - chandoo.org/wp/15prompts
🕐Video Timestamps:
0:00 - How I use AI?
0:38 - How I don't use AI?
1:55 - Generating and testing sample data with AI
4:20 - AI for Design and Prototyping stages of projects
7:19 - AI for coding and automation
10:28 - Getting a headstart on new projects with AI
12:03 - AI for documenting my work
13:20 - In conclusion - What I do and don't with AI
🤖 AI + Data - More videos:
How AI does my work (🤫 Don't tell my boss) - youtu.be/VOvIu5lKoBQ
Work 10X faster in Excel with AI features - youtu.be/xGqH__hLVcU
ChatGPT made this dashboard - youtu.be/mp18miYmmJY
How to use ChatGPT to improve your Excel Skills - youtu.be/EsT5TNwbBVM
💥 Download Chandoo's Prompting Cheatsheet 👉 chandoo.org/wp/15prompts
~
#excel #powerbi #ai #chatgpt
1️⃣ Make a few distinct versions of your CV and test them out. Improve one that has most traction
2️⃣Don't load up the resume with all sorts of jargon & buzz words. Focus on a few key tech (like SQL, Power BI, Snowflake, Azure) and really go deep.
3️⃣Say what you have done, not what you are asked to do. List your accomplishments instead of job responsibilities in the CV.
#resumetips #dataanalyst
What we are going to learn:
1) Explanation of the data model
2) How and why we use ALL funciton
3) Using ALL(table) vs. ALL(table[column]) with a real-world example
4) DAX Query View to debug and check measures
5) Understanding the "evaluation" context and why sometimes doesn't work
6) ALL vs. ALLSELECTED
7) ALL vs. ALLEXCEPT
8) ALL vs. REMOVEFILTERS
9) Understanding ALLCROSSFILTERED & ALLNOBLANKROW\
10) Practical Example #1: Rank among all vs. Rank in a specific category (ALL with RANKX)
11) Example #2: How a "specific" month sales vary compared to "all months" sales
📁 Sample Power BI File:
Blank file: github.com/chandoo-org/Power-BI/raw/refs/heads/main/sample%20files/All%20the%20ALL%20functions-blank.pbix
Completed file (available only for my Power BI Course members): join here 👉 chandoo.org/wp/power-bi-course
🕐 Video Topics:
0:00 - Understanding all the ALL functions in DAX (Power BI)
0:45 - Why and when to use ALL function
3:01 - Using ALL() with a table vs. table column
4:50 - DAX Query View and how it helps you with understanding calculations
6:10 - The key to understanding DAX - Evaluation Context
9:49 - ALL vs. ALLSELECTED
13:27 - ALL vs. ALLEXCEPT
17:20 - ALL vs. REMOVEFILTERS
18:40 - ALLCROSSFILTERED and ALLNOBLANKROW
22:14 - Rank in all vs. Rank in Category - RANKX + ALL Example
26:01 - Specific month sales vs. all month sales - Variance Analysis
📺 More Power BI Videos and Playlists
💥 Complete Power BI Course: If you want to learn Power BI including Power Query, DAX, Data Modeling and Dashboards - Check out my comprehensive Power BI Course - chandoo.org/wp/power-bi-course
Use Code SAVE100 to get upto $100 (or Rs 6,000) discount on my Power BI + Excel bundle.
🎶 Power BI Videos playlist - youtube.com/playlist?list=PLmejDGrsgFyClheDmsfikqOt0no-TKArs
🎶 DAX + Power Pivot Playlist - youtube.com/playlist?list=PLmejDGrsgFyCm0i58VobdriBr--FA8lYh
~
#powerbi #dax
Use WRAPROWS to do the job, like a BOSS 😎
Thank me later...
#excel #datacleaning
Gone are the days of manual data cleaning. Nowadays, Excel offers amazing functions to automate the data cleaning process. In this masterclass, learn the super tricks and become a data cleaning PRO.
✅ Reshaping data with WRAPROWS and TAKE
✅ Stacking data with HSTACK and VSTACK
✅ Removing excess spaces with TRIM
✅ Replacing typing errors and synonyms with a mapping table and XLOOKUP
✅ Replacing missing values with IF function
✅ Extracting values from text using TEXTSPLIT, TEXTBEFORE and TEXTAFTER functions
✅ Date formatting with DATEVALUE
✅ Using IFERROR & SUBSTITUTE to deal with annoying date problems
✅ Using REGEXTRACT to get data that matches the pattern we want
✅ Non-REGEX formula with TEXTSPLIT
✅ Removing duplicate data values with UNIQUE function
📁 Sample Data File:
Download the data file and practice all these functions yourself - chandoo.org/wp/wp-content/uploads/2025/05/data-cleaning-super-tricks.xlsx
🕐 Video Topics & timestamps:
0:00 - Excel Data Cleaning Supertricks
2:31 - Using WRAPROWS to reshape data
4:12 - Combining both datasets using VSTACK
7:00 - Cleaning the Person column with TRIM & CHOOSECOLS
8:14 - Replacing errors & synonyms in City column with XLOOKUP
10:38 - Using LET & IF functions to clean the price data
12:15 - Extracting "date" of the event from event detail column
14:58 - Converting date to correct format with DATEVALUE
16:38 - Fixing errors with Date Conversion
20:04 - Using REGEXTRACT to extract the event time details
25:10 - non-REGEX formula for getting the event time (using TEXTSPLIT and wildcard XLOOKUP)
28:36 - Removing duplicate records with UNIQUE function
🧹 More videos on Data Cleaning:
Data Cleaning in Excel - 10 Amazing Tricks - youtu.be/q7EpoOwBcnM (400k views, must watch)
Data Cleaning in Excel - 10 PRO tricks - youtu.be/SNVjndgWBlw (500k views, must watch)
How to clean, analyse and visualize data - youtu.be/H6k28jhclwI (450k views, long-form)
Power Query in 15 mins - youtu.be/UAFExySaSPY (450k views, must watch)
Power Query in 30 mins - youtu.be/MMdcczmULrU (200k views, NEW)
Power Query Masterclass - youtu.be/PiFAa_jjaEI (700k views, long-form)
🧑💻 Complete Adv. Excel Course:
Want to learn formulas, Power Query, charting, pivot tables, Power Pivot, DAX, dashboards and advanced features - all in one place?
Check out my Excel School program. You will love it.
💥Course: chandoo.org/wp/excel-school-program
💰Discount: use code 10101010 to get 10% discount
~
#excel #datacleaning #function
0:24 - TRICK 1: Auto number as you type
3:09 - TRICK 2: Auto highlight when values change more than 10%
6:37 - TRICK 3: Make sure the total adds up to 100%
💥 Want more? Check out my Advanced Excel Course - chandoo.org/wp/excel-school-program
🎟️ GET10% discount with code 10101010
#excel #tricks
Also learn how to visualize data from both tables in matrix and "new" card visuals with powerful story-telling tricks.
💥 Sample file (demo): app.powerbi.com/view?r=eyJrIjoiODY2N2RiYzUtZjYzNy00OTVmLTgxZDctNjQ0NTA5ZDQxYWYyIiwidCI6IjUzNTA4ZDUyLWQxYjAtNDliMC1iNGJhLTM1MzNjMTI0OWEwMSJ9
📁 Raw data: github.com/chandoo-org/Power-BI/blob/bc089ea4514d84221fd174a564fd7156177dbc43/insurance_dashboard/motor_data14-2018.zip
🔒Complete workbook: Available in my online Power BI class
🧑💻 Join my Power BI Class (get $100 / $50 discount): Visit chandoo.org/wp/power-bi-course to sign up for my complete, step-by-step Power BI course. Use coupon code SAVE100 to get $100 or Rs 6,000 discount on the course fees.
🕐 Video timestamps:
0:00 - More than one relationship between tables (how they look)
0:45 - About the dataset (insurance policy data)
3:10 - The relationships between tables - active vs. inactive
4:03 - Creating DAX measures to analyze data
5:52 - USERELATIONSHIP DAX function
8:58 - Matrix visual with net policy gain trends
13:45 - You are going to LOVE 😍 this
14:48 - "NEW" Card Visuals and showing Individual Year Values
18:55 - Reference Labels & Conditional Formatting
21:10 - Dynamic Card Titles
22:30 - Formatting the cards
24:28 - Custom interactions
~
#powerbi #datavisualization #dashboards
📁 Sample file + resources: chandoo.org/wp/how-to-svg-dax-measures-power-bi
🗃️ Ready to use SVG Templates: kerrykolosko.com/portfolio
🎨 SVG Editor: boxy-svg.com/app
😍 Calendar Chart Inspiration: public.tableau.com/app/profile/bigxpt/viz/SALARYWORKHOURS/SALARY
~
#powerbi #dax #svg
📁Sample file: chandoo.org/wp/wp-content/uploads/2025/04/male-vs-female-chart-demo.xlsx
💥Adv. Excel Course: chandoo.org/wp/excel-school-program
🤯 Things you will learn in this video:
☑️ How to set up Male & Female "stencils" in PowerPoint
☑️ Using XLOOKUP + Data Validation to create a dynamic chart in Excel
☑️ Axis formatting tricks to get the scaling right
📺 Other Charting Videos You WILL Enjoy:
Colum Chart with Totals - youtu.be/IcbS1E-7FGA
TopN Chart in Power BI - youtu.be/uQV5MP_THfA
Map Charts in Excel - youtu.be/qNNn5uiZxOE
Variance Charts in Excel - youtu.be/REpNnsdHxlY
Clickable Chart in Excel - youtu.be/L3WFN0bGv8I
#excel #chandoo #charting #dashboards
Full video - youtu.be/IcbS1E-7FGA
#excel #chandoo
Also learn how to add cool "emoji" based data labels to improve the story-telling.
Sample file - chandoo.org/wp/wp-content/uploads/2025/04/column-column-chart-demo.xlsx
~
Learn the DAX concepts for performing New vs. Returning customer analysis in Power BI.
✅ Calculate Unique Customer Count
✅ Get "returning" customer count using SUMX and CALCULATE
✅ Alternative approach of using INTERSECT dax function
✅ Visualizing customer data
✅ Enhancing customer analysis with "windows"
📁 Sample Power BI file
https://1drv.ms/u/c/e7c6dec249ad257b/EWnD_9DRzfpIsdrF54oGvP4BQ5f20TpogwzBEX4o7oyKMQ?e=2OjfcT
📁 Sample Data file (to write your own DAX)
https://1drv.ms/x/c/e7c6dec249ad257b/EbvD-ce90dNPnSyhitPH5HwBYi-3_ThtAT9KWTe0cWSD-w?e=8XtRKN
💥 FREE Live Session on Power BI this weekend
Sign up here - chandoo.org/wp/liveclass
~
#powerbi #chandoo #dax
~
In this video:
✔️ How to design the Power BI report for EIC insurance company
✔️ Writing DAX to calculate important KPIs
✔️ Using "NEW" card visual and adding conditional formatting
✔️ Custom bar chart design
✔️ Emoji trick for AWESOME labels
✔️ Working with Ribbon charts
✔️ Layering "donut" charts to make an impossible chart
✔️ Using Scatter Plots + Symmetry Shading to find insights
✔️ Finding profitable segments for insurance with matrix + conditional formatting
✔️ Creating and linking tool-tip pages
✔️ Customizing interactions and filter behaviours
✔️ Lots of handy little tricks and tips
Sample data & blank template file - github.com/chandoo-org/Power-BI/tree/main/insurance_dashboard
#Powerbi #dashboards
Step 1: Sign up for a free trial of another Microsoft product like Dynamics 365 (microsoft.com/en-us/dynamics-365/free-trial)
Step 2: Set up your own free sub-domain on the onmicrosoft.com
Step 3: Use that to access Power BI service and publish / share your work with others.
Step 4: Join my Power BI course and master the technology - chandoo.org/wp/power-bi-course (use code SAVE50 to get $50 discount)
~
My Power BI videos:
Playlist: youtube.com/playlist?list=PLmejDGrsgFyClheDmsfikqOt0no-TKArs
Course: Join my Power BI course and master the technology - chandoo.org/wp/power-bi-course (use code SAVE50 to get $50 discount)
#powerbi
What you are going to learn 💡
========================
1) The challenge of "tagging" or "categorization" based on rules table.
2) Using Text.Contains and the limitation
3) Introducing "loops" ♾️ in Power Query with List.Accumulate()
4) Using List.Accumulate with a custom function
5) How to convert our rules logic into an invokable M function for future uses
6) Enhancement ideas
Sample file 📂
============
chandoo.org/wp/wp-content/uploads/2025/03/expense-categorization.xlsx
Video Timestamps 🕐
===================
0:00 - The ultimate Power Query time-saver trick
1:20 - Loading the data into Power Query
2:20 - Solving for one line item - understanding the logic
4:01 - Using Text.Contains and the limitation
4:55 - List.Accumulate - What is it and how to use it
11:30 - Converting the logic to a Power Query function
19:25 - Ways to enhance the functionality
💰$50 Discount on My Power Query, Power Pivot & Power BI course 🤑
==========================================================
If you like this magic show, you are going to LOVE my online, self-paced Power BI course. It helps you unlock the hidden powers & secrets of Power Query, M language, Power Pivot, DAX & Power BI report building. Visit chandoo.org/wp/power-bi-course
Use Coupon code SAVE50 to get $50 (or Rs 3,000) discount.
~
#powerquery #chandoo
📁 Sample file: chandoo.org/wp/wp-content/uploads/2025/03/vlookup-any-sheet.xlsx
💻 10% OFF My Excel Course - chandoo.org/wp/excel-school-program 🎟️ use code SUMPRODUCTIF
In this video:
1) How to lookup across multiple sheets
2) Introducing VSTACK to combine all the data
3) Using LET and variables
4) Getting Qty, Date & other details with one spillable formula
5) What if multiple matching values are there - using FILTER
🕛 Video timestamps:
0:00 - VLOOKUP across multiple sheets - the problem
1:26 - VSTACK + XLOOKUP solution
3:45 - Two issues with this approach
4:24 - How to get other columns data too
5:58 - Getting "all" matching results from across sheets
🧮 Functions used and explained in this video:
======================================
XLOOKUP
VSTACK
LET
CHOOSECOLS
TRANSPOSE
FILTER
🧑🎓 Want to learn Excel for real?
==========================
Do you want to learn how to use Excel for doing "actual" work? Check out my Excel School program and unlock your potential. I focus on practical examples like this and share real-world skills for your success.
GET 10% DISCOUNT with the coupon code SUMPRODUCTIF
Visit chandoo.org/wp/excel-school-program to join
~
#excel #vlookup
📂 Sample file: chandoo.org/wp/wp-content/uploads/2025/03/sample-file-done.xlsx
Stream timing:
Wellington (NZ ) Sunday, 2 March 2025 at 2:00 p.m.
New Delhi (India) Sunday, 2 March 2025 at 6:30 a.m.
New York (USA - EST) Saturday, 1 March 2025 at 8:00 p.m.
Chicago(USA - CST) Saturday, 1 March 2025 at 7:00 p.m.
Seattle (USA – PST) Saturday, 1 March 2025 at 5:00 p.m.
London (UK - GMT) Sunday, 2 March 2025 at 1:00 a.m.
Singapore Sunday, 2 March 2025 at 9:00 a.m.
Dubai (UAE) Sunday, 2 March 2025 at 5:00 a.m.
Sydney (Australia – AEDT) Sunday, 2 March 2025 at 12:00 Noon
Share it with your manager...
#excelformanagers #excel
Sample file: chandoo.org/wp/wp-content/uploads/2025/02/byrow-is-a-beast.xlsx
Learn more: chandoo.org/wp/how-to-lookup-in-any-column-excel
Video topics:
1) What is BYROW() formula in Excel and how to use it?
2) BYROW() with LAMBDA()
3) Using BYROW() to lookup in any column (advanced trick)
Sample file: chandoo.org/wp/wp-content/uploads/2025/02/cumulative-average.xlsx
How would you do this? Leave a comment and let me know...
#chandoo #exceltips
The key skills you want to gain are:
📚 Data engineering & SQL
📚 Coding (for data - like Python)
📚 User interaction skills
📚 Business domain skills
📚 Other BI / Analysis tools - Tableau, Excel, Python
📚 Forecasting, data insights
📚 AI, Machine learning, GPTs
Videos you should check out:
SQL - youtu.be/l8DCPaHc5TQ
Data Engineer vs Data Analyst - youtu.be/ZyRH6FMcVlw
What is ETL and data engineering - youtu.be/wDTzxdShbd8
Power BI ecosystem - Explained - youtu.be/mO-fvZPg7-Q
Python for data people - youtu.be/gaPFwH0r4uo
How to use Tableau - youtu.be/tNLp_JVipoQ
I wish you all the best in 2025.
#powerbi
All in one video. Learn everything, practice the concepts and improve your skills in just 4 hours. This is my holiday gift for you 🎁
📁 Download the files: chandoo.org/wp/complete-excel-course-free
Topics covered in this video:
Power Query
Web scraping
Data cleaning & automation
Adding columns
How to use Excel
Conditional formatting
Quick analysis of data
Autosum
Excel Tables
Excel functions & formulas
COUNTIFS, SUMIFS, AVERAGEIFS
XLOOKUP
FILTER
INDEX
CHOOSECOLS
IFERROR
SMALL, LARGE
SORT, UNIQUE
Statistical analysis of data
Excel pivots
Slicers, timelines
Interactive pivots
Interactive charts
Pivot charts
Pivots + conditional formatting
Excel charts
Excel data model (power pivot)
DAX in Excel
Creating a dashboard in Excel
Chart customization
Using pictures in Excel
Dynamic dashboards
+ hundreds of tips, tricks, shortcuts and ideas 💡
🚀 Learning Progression:
0 - 15 mins: How to use Excel
15 - 48 mins: Power Query and Data Management
49 - 1 hour 50 mins: Excel formulas & functions
1h 50 - 2h 40 mins: Excel Pivot Tables, Slicers & Charts
2h 40 - 4h: Excel portfolio project
🕐 Detailed Timestamps:
00:00 - How to use Excel
14:36 - Power Query and Data Management
49:27 - Excel formulas & functions
1:53:39 - Excel Pivot Tables, Slicers & Charts
2:42:47 - Excel portfolio project
Enjoy the video and download all the workbooks and additional resources here - chandoo.org/wp/complete-excel-course-free
#Chandoo #excel
What we are going to learn:
1) Programming concepts like data types, objects and control flow
2) How to use for loop to loop through list of items
3) A cool trick to write faster and better loops
4) Using list comprehension - alternative to loops
👩💻 Code used in this video:
github.com/chandoo-org/python
🕐 Video timestamps:
0:00 - Introduction to looping and list comprehension
0:15 - 3 Main concepts in programming
0:38 - Data types
2:10 - Objects & Classes
3:15 - Control flow
5:18 - Using for loop in Python
8:19 - Printing index number & sport with loops
13:38 - A faster & better way to write loops with enumerate()
16:13 - List comprehension in Python
21:16 - What's next?
Introducing the SEQUENCE() in Excel. It puts FUN in Functions. We can use it to get numbers, texts, dates and even improve your formulas.
5 AWESOME examples to get started:
⬇ SEQUENCE(1000) - makes 1000 numbers row-wise
➡ SEQUENCE(,1000) - makes 1000 numbers column-wise
🔠 SEQUENCE(10, 10) - makes 10x10 grid of numbers, 1 to 100
🟠 ="INV" & SEQUENCE(1000) - makes 1000 invoice numbers, starting with INV1, INV2... INV1000
📅 SEQUENCE(365,, "1-JAN-2025") - makes all the 365 days of 2025
Video topics:
0:00 - Why dragging is bad for you
0:27 - How to make 1000 numbers quickly!
1:16 - Adding prefixes like INV for invoice numbers
2:15 - Using SEQUENCE to make all the dates of 2025
2:55 - Six packs on the fingers 😂
3:12 - My absolute favourite trick with spill ranges
4:14 - How to add up all the spilled values
📺 Watch next:
============
Using # operator in Excel (Spill ranges) - youtu.be/kR6czKY4FEw
Stop using Pivot Tables - youtu.be/SPFQX82X03Q
I don't use VLOOKUP anymore! - youtu.be/DDlZXXEI1bU
Excel LAMBDA function is a must have - youtu.be/YrMCkE59ef4
FILTER() Function is a must have - youtu.be/JuTdj2j-9Kg
~
If I can sing 🎶 about it:
We don't drag any more...
We don't drag any more
Like we used to do.
#chandoo #chan2 #exceltips
Key skills you will gain are:
✔ How to install Visual Studio Code & Python
✔ How to set up virtual environment for Python in VS Code
✔ How to set up and use Jupyter Notebooks with VS Code
✔ Writing and running your first python code
✔ Using "notebook" style programming to learn Python
✔ Working with Python Lists & slicing
✔ Control flow with IF conditions in Python
✔ Building a simple grade calculator with Python
✔ Next steps for Python leaning (lists, comprehension, numpy, matplotlib)
💼 Resources for you:
==================
💻 Download VS Code: code.visualstudio.com
🐍 Download Python: python.org/downloads
👩💻 Code samples: github.com/chandoo-org/python/blob/main/my%20first%20python%20examples.ipynb
💡 Free Data Analyst Course Page: chandoo.org/wp/free-data-analyst-course
💥 Courses:
Advanced Excel: chandoo.org/wp/excel-school-program
Power BI: chandoo.org/wp/power-bi-course
Power BI + Excel Combo Course: chandoo.org/wp/power-bi-course
📺 Other Python videos:
Learn Python in an hour: youtu.be/uoC48wrJ-yM
Learn Pandas in 15 mins: youtu.be/mkYBJwX_dMs
Python vs. VBA - which is better: youtu.be/l0o7VMEf0yc
Using Python in Excel: youtu.be/whzmtv9qfIQ
🕐 Video Timestamps:
===================
0:00 - What to expect in this video?
0:23 - Installing Visual Studio (VS) Code
2:59 - Installing Python
4:36 - What is VS Code and how to use it?
6:48 - Our first python code
8:10 - Setting up "Virtual Environment" in VS Code
13:06 - Adding Jupyter Notebook Support in VS Code
16:00 - Creating our first Jupyter Notebook in Python
23:35 - Working with variables in Python
26:00 - Using f strings in Python
27:13 - Using Jupyter tab in VS Code to "inspect" the variables
30:15 - Dealing with errors in Python Notebooks
32:14 - Working with Python "Lists"
40:10 - IF condition in Python + a mini project
49:34 - What's next?
~
#python #coding #dataanalytics #fdac #chandoo
Column chart 📊
Line chart 📈
Donut charts 🍩
KPI Cards 💳
Small Multiples 🔡
Matrix 📰
📁 Blank Power BI file - files.chandoo.org/pbix/powerbi-visuals-part1-blank.pbix
🖼 Background Image - files.chandoo.org/pbix/img/Slide2.PNG
💡 Completed workbook + more in my Power BI course - chandoo.org/wp/power-bi-course
~
🕐 Video Timestamps:
===================
0:00 - Introduction and what you are going to learn
1:03 - How to set canvas background in Power BI?
2:26 - Column charts in Power BI
5:46 - Line charts for trend analysis in Power BI
10:24 - Donut charts in Power BI
12:04 - KPI Card visual in Power BI (new card)
17:05 - Small Multiples in Power BI
21:33 - Matrix visual in Power BI
24:29 - How to learn more?
💼 Resources to learn Power BI:
===========================
💡 My Power BI course - chandoo.org/wp/power-bi-course
Power BI Videos (playlist) - youtube.com/playlist?list=PLmejDGrsgFyDMME3o2CamamZ8w9NxSWWo
Power BI End-to-End Tutorials - youtube.com/playlist?list=PLmejDGrsgFyCqFbX3io5q8haeF7BUzWDP
Free data analyst course playlist - youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
~
#powerbi #dashboard
~
Make the perfect entry level data resume with three easy steps.
Step 1: Set up your resume for success with a clear and well-defined template. Then customize it for the job you are applying for.
Step 2: Build Impact with Limited Experience by highlighting "transferable" skills, using ATR style bullet points and creating a strong summary to reflect your passion.
Step 3: Stand out with personal branding and social media presence by setting up LinkedIn & GitHub profiles, double checking everything on the resume.
Step 4 (optional): Use my free Resume template & resume checker (AI) tool to see if your resume is a good fit for the job.
Notion template link 👉 https://rain-speedwell-127.notion.site/Job-Resume-Match-1228f52290ec801aae70e44f0cbf73c6?pvs=4
💼 Resources for you:
==================
📃 Notion template - https://rain-speedwell-127.notion.site/Job-Resume-Match-1228f52290ec801aae70e44f0cbf73c6?pvs=4
💻 Learning:
Data analyst Roadmap (12 weeks) - youtu.be/R3bZ0LLv3Fk
Free data analyst course - youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
🎤 Interview prep:
Mistakes to avoid in the interviews - youtu.be/f7lBYh1_9BQ
Power BI Interview questions - youtu.be/aZihsRsEYTE
Data analyst interview questions - youtu.be/MX6Ee_ubE1s
Sample technical question - youtu.be/cMEvyhvd5Es
👩💻 Courses:
Power BI course - chandoo.org/wp/power-bi-course
Excel Course - chandoo.org/wp/excel-school-program
⏱ Video topics:
=============
0:00 - 3 steps to make a stunning entry level resume
0:18 - Step 1 (setting up for success)
3:17 - Step 2 (build impact with limited or no experience)
4:09 - Jo on how she hires
5:40 - How to write ATR Bullet Points
7:38 - Jonathan on how he hires
9:34 - Step 3 (standing out from the crowd)
13:04 - My Notion Template and how to use it
~
👍 All the best with your job search.
#resume #dataanalyst #jobsearch
~ important links ~
📁Sample blank file - files.chandoo.org/pbix/blank-report-v2.pbix
💡 Join Power BI Course - e-junkie.com/i/xbbt
💻 Know more - chandoo.org/wp/power-bi-course
📚 In this video:
=============
🎥 Introduction to Live Streaming and Power BI: The session begins with a live streaming setup, greeting participants, and introducing the Power BI session.
📁 Setting Up the Power BI Report: A hotel reservation dashboard is created, illustrating step-by-step setup of metrics, slicers, and background for the report.
💡 Exploring Data Preparation Techniques: Learn about data cleaning and preparation, including the calculation of relevant columns such as booking and stay dates.
📊 Visualization Techniques: Key visuals such as trend lines, slicers, heatmaps, and tables are used to display booking trends, customer segments, and insights.
🔧 Adding Aesthetic Features: Background customization, theme setting, and color scheme creation in Power BI are demonstrated to enhance report visuals.
📈 Advanced Data Analysis: Insights on weekday vs. weekend bookings, loyalty levels, booking channels, and customer behaviour are included.
📑 DAX Calculation Guidance: Overview of using DAX functions like SUM, COUNT, AVERAGE for calculating metrics and deriving insights.
🕐 Topic Timestamps:
===================
0:00 - Welcome to the livestream
8:03 - Starting with the blank (sample) file to build hotel reservation report
10:52 - A look at the Power Query to get the data
13:15 - A closer look at the model & measures
15:50 - KPI card construction explained
16:20 - How to build the reports (mock-up process) & what we are going to build
18:45 - Adding canvas background
20:45 - Stay trend over time
31:35 - Weekday wise stay trend
38:55 - Who stays with us? (by loyalty program)
47:32 - How far ahead the bookings are made?
53:05 - How I am using Power BI report themes?
55:47 - Customer Analysis (matrix visual)
1:01:10 - Adding conditional formatting to the matrix
1:03:02 - A look at the final report and extra story-telling bits
1:06:27 - How to learn Power BI properly?
1:27:00 - Q&A
💥 My Power BI Course:
Save $100 on my Power BI course (pay $199 instead of $299, or Rs 13,500 instead of Rs 18,000). Offer valid until 30th of November only.
Get the course here 👉 chandoo.org/wp/power-bi-course
~
#powerbi #chandoo #powerbidashboard
→ How to create measures 🤔
→ Measure vs. Column ⚙
→ Measure reusability ♻
→ Evaluation Context 💻
→ Basic DAX functions - SUM, COUNTROWS, AVERAGE, DIVIDE 🧮
→ Conditional DAX functions - IF 🌿
→ Understanding and using CALCULATE function 📗
→ Multiple filters with CALCULATE 🥝
→ How to get good with DAX using ACMBU technique 💡
📁 Download practice workbooks:
=============================
Blank Power BI file (with data model + data) - chandoo.org/wp/wp-content/uploads/2024/10/powerbi-data-model-done.zip (unzip this)
Sample dataset - chandoo.org/wp/wp-content/uploads/2024/10/sample-chocolate-sales-data-all.xlsx
Completed Power BI file (and rest of the videos + files) - chandoo.org/wp/free-data-analyst-course
⏱ Video Chapters:
================
0:00 - Introduction to DAX & our data model
3:00 - Our first DAX measures
11:24 - Why we need DAX when you can get the same thing with "auto" calculations
13:18 - DAX Pledge (say it out with me 🗣)
13:39 - Measure vs. Columns
15:08 - More DAX formulas
16:08 - Difference between Excel and DAX functions!
19:24 - Understanding Evaluation Context
25:12 - Reusing DAX measures
28:32 - Why not use table name in the measures?
30:56 - DIVIDE function
34:18 - CALCULATE function
42:21 - More than one filter with CALCULATE()
46:35 - IN operator
49:22 - Conditional logic with IF()
52:51 - Measures are SCALAR (Aggregates)
55:29 - Awesome DAX trick 💡
58:20 - ACMBU Technique for Better DAX 😎
👩💻👨💻 Join my Power BI Course
==========================
Want to master all aspects of Power BI - right from Power Query to Data Modeling to Power Pivot to DAX to Visual design to Report building?
Then you are going to LOVE my Power BI Course.
Trusted by more than 1000 people all over the world and consistently rated 5* by everyone.
Check out the program and sign up today.
Go here 👉 chandoo.org/wp/power-bi-course
📺 More Power BI Videos on my Channel:
=================================
Previous concepts:
Data modeling: youtu.be/4ePNrdxWtY0
Introduction to Power BI: youtu.be/al4OfLIOl2w
Introduction to Power Query: youtu.be/MMdcczmULrU
Power BI Jargon Explainer - youtu.be/mO-fvZPg7-Q
Power BI Jargon Explainer (part 2) - youtu.be/6eQL_GnFXls
VIDEOS:
Power BI Sales Dashboard in an hour - youtube.com/live/mXD0Peot4hY?feature=share
Data analysis with Power BI (end to end live) - youtube.com/live/8tfcVnoEL0c?feature=share
Power BI end-to-end Course - youtu.be/uwe8C7K8fXY
Power BI + SQL how to - youtube.com/live/UQJAHUUFK-o?feature=share
How to use DAX (another video, just as awesome) - youtu.be/PQ5lALdFHCU
Complex Power BI Dashboard (end to end tutorial) - youtu.be/ooJO7NW4uJU
Power BI Playlists:
FREE Power BI Course - youtube.com/playlist?list=PLmejDGrsgFyDMME3o2CamamZ8w9NxSWWo
Power BI tips & examples - youtube.com/playlist?list=PLmejDGrsgFyClheDmsfikqOt0no-TKArs
Power BI DAX - youtube.com/playlist?list=PLmejDGrsgFyCm0i58VobdriBr--FA8lYh
Power BI Power Query - youtube.com/playlist?list=PLmejDGrsgFyBqyHBq4jqdUzobCo5BDKY2
COURSES:
Power BI - chandoo.org/wp/power-bi-course
Adv. Excel - chandoo.org/wp/excel-school-program
Complete DATA Analysis course (Sql, Power BI, Excel, Python) - chandoo.org/mdac
~
CALCULATE(), FILTER() and ALL() wanted to have dinner together.
But it never happened.
Why?
Because FILTER() kept excluding people
ALL kept ignoring everything
and CALCULATE as usual was late 😂
#Powerbi #dax #chandoo #freedataanalystcourse #fdac
In this video learn about data modeling in Power BI, using a fictional chocolate company as an example. It explains the importance of setting up a star schema, which involves organizing data into fact and dimension tables. I also cover the process of importing data into Power BI, establishing relationships between tables, and creating visualizations to analyze the data.
💡 What you will learn:
==================
◉ What is Data Modelling:
◉ Data modelling with a real-world example
◉ Fact vs. Dimension table
◉ How to set up relationships in Power BI?
◉ Types of relationships
◉ What does the "arrow" mean in the semantic model? (filter propagation)
◉ Importance of "correct" data modelling
⏱ Video timestamps:
==================
0:00 - Introduction to Data Modeling (or Semantic Modeling)
0:43 - Sample dataset and the tables
2:18 - Data model for Awesome Chocolates business 🍫 (fact vs. dimension, star schema)
9:40 - Loading data into Power BI
11:50 - Creating the semantic (data) model in Power BI
15:35 - What is filter direction & filter propagation in Power BI?
18:08 - Evaluation context explained with data model
20:19 - The importance of "proper" data modeling
21:32 - In conclusion
📁 Sample files:
=============
Awesome chocolates dataset (Excel file) - chandoo.org/wp/wp-content/uploads/2024/10/sample-chocolate-sales-data-all.xlsx
Completed Data Model Power BI workbook and other resources - chandoo.org/wp/free-data-analyst-course
Free 14-week Data Analyst Course - chandoo.org/wp/free-data-analyst-course
💻 Paid courses to help you:
=======================
Modern Data Analyst Course - chandoo.org/mdac (Power BI, SQL, Excel, Python & AI)
Power BI course - chandoo.org/wp/power-bi-course
Excel course - chandoo.org/wp/excel-school-program
~
#powerbi #datamodeling #chandoo #freedataanalystcourse #fdac
In this video we will be covering,
▶ Introduction to Power BI: how to get started with Power BI, including downloading and installing Power BI Desktop, loading data, and creating visuals and tables.
▶ Creating a Business Report: step-by-step process of creating a complete business report using Power BI, from loading sample data to formatting visuals and sharing the report with others.
▶ Power BI Interface: the main areas of the Power BI interface, including the ribbon, canvas, panels, and switching panel, and how to use these areas to construct and navigate reports.
▶ Building Visuals: how to create various types of visuals, such as column charts, bar charts, and slicers, and how to format and customize these visuals to display data effectively.
▶ Publishing and Sharing Reports: the process of saving, publishing, and sharing Power BI reports, including the benefits of using the Power BI online platform for collaboration and data updates.
💡 Important Links:
================
Download Power BI - microsoft.com/en-us/power-platform/products/power-bi/desktop
My paid Power BI course - chandoo.org/wp/power-bi-course
Sample files & other info - chandoo.org/wp/free-data-analyst-course
⏱ Video Chapters:
================
0:00 - Introduction to Power BI
0:59 - How to download Power BI?
1:54 - Loading data into Power BI
3:19 - Understanding the Power BI Screen
7:20 - Let's make our first Power BI chart
10:00 - how to format the charts?
11:54 - Making a second graph (using Power BI's auto build option)
12:42 - Most "fun" and "useful" aspect of Power BI
14:16 - Changing calculation type in the visual
15:08 - Using the "Card" visual in Power BI
16:30 - How to apply a slicer?
17:34 - Calendar slicer
18:25 - Let's make a business report
20:22 - Creating the trend chart
23:40 - Sales amount by country (donut chart)
24:20 - Sales person performance as a table
27:36 - Product sales report (bar chart)
30:50 - Adding KPI cards for key metrics
32:15 - Slicers for the report
33:05 - Saving and publishing the Power BI report
36:49 - Coming up...
📁 Sample Data File:
=================
Data file: chandoo.org/wp/wp-content/uploads/2024/10/sample-chocolate-sales-data-1.xlsx
Completed Power BI Workbook: chandoo.org/wp/wp-content/uploads/2024/10/power-bi-intro-demo.zip
More info and the full course materials: chandoo.org/wp/free-data-analyst-course
~
#powerbi #chandoo #FDAC #freedataanalystcourse
Watch part 1 👉 youtu.be/mO-fvZPg7-Q
~
Power BI is like an iceberg. There is a little we can see, but most of its complexity and technology is hidden underneath. So, in this video, let me demystify Power BI by explaining 50 of the most common and confusing jargon you hear. You will learn how the architecture of Power BI technology looks by the end of this video. This is the part 2 of the video. Watch part 1 here - youtu.be/mO-fvZPg7-Q
I have explained all the main areas of Power BI in the video:
Power BI
Power BI service
Power Query
Power Pivot
Cloud
Data infrastructure
Fabric
Automation
and AI
This is the Part 2 of the 2-part video series.
In this video, the following terms are explained:
Power BI Service
RLS (Row Level Security)
Publishing a Report
Dataset
Sharing a report
Workspace
Scheduled Refresh
Users
Power BI Pro & Premium
Gateway
Power Platform
Power Automate
Power Apps
Low-code
CoPilot
AI visuals
DAX Query View
Cloud Platforms
Data Engineering
ETL
Data Flows
Pipeline
Real-time data
Streaming data
Notebook
⌚ Video Chapters:
================
0:00 - Power BI jargon explanation (and free PDF for you)
0:55 - Power BI Service (platform, RLS, publish)
7:44 - General Data Terminology (data engineering, pipelines, etl etc.)
11:04 - Power Tools, Cloud and AI terminology (Power Automate, CoPilot, Azure etc.)
16:57 - In conclusion - Power BI terminolgy
💡 More Power BI:
===============
💥 Advanced Power BI Course - chandoo.org/wp/power-bi-course
Learn Power BI with me and master the technology.
Part 1 video - youtu.be/mO-fvZPg7-Q
Getting started with Power BI - youtu.be/9tF1IrfLflg
Power BI end-to-end project - youtu.be/uwe8C7K8fXY
How to use Power Query - youtu.be/MMdcczmULrU
How to use Power Pivot - youtu.be/PQ5lALdFHCU
What is Fabric - youtu.be/--cK4A2djoA
20+ Data & BI Terms explained - youtu.be/a906b80lCE8
For more info:
===========
📃 my website chandoo.org/wp
💻 my course chandoo.org/wp/power-bi-course
🤝 my linkedin linkedin.com/in/purnaduggirala
📷 my insta instagram.com/chandoo.xlsx
📧 get in touch chandoo.org/wp/contact
~
#powerbi #jargon #chandoo
~
Power BI is like an iceberg. There is a little we can see, but most of its complexity and technology is hidden underneath. So, in this video, let me demystify Power BI by explaining 50 of the most common and confusing jargon you hear. You will learn how the architecture of Power BI technology looks by the end of this video.
I have explained all the main areas of Power BI in the video:
Power BI
Power BI service
Power Query
Power Pivot
Cloud
Data infrastructure
Fabric
Automation
and AI
This is the Part 1 of the 2-part video series.
In this video, the following terms are explained:
Connections
Query
Table
OneLake
Warehouse
Database
Datamart
Dataverse
SQL
Transformation
Refresh
Query Folding
M Language
Close & Load
Query Parameter
Power Pivot
Modeling
Data vs. Semantic Modeling
DAX
Measure
KPI, Metric & Calculation
Relationship
One to many relationship
Filter Direction
Star Schema
Snowflake Schema
Evaluation Context
Power BI Canvas
Visual
Report
Dashboard vs. Report
Filters
Slicer
Drillthrough
Interaction
Paginated Report
Conditional Formatting
💥 The second part is here - youtu.be/6eQL_GnFXls
⌚ Video Chapters:
================
0:00 - Power BI is like an iceberg
1:02 - Power BI as a mind-map
2:57 - The data layer of Power BI (power query, connections, M etc.)
11:30 - How I learn and remember technical concepts
13:40 - Calculation layer of Power BI (semantic modelling, relationships, DAX, Power Pivot etc.)
20:14 - Reporting layer of Power BI (visuals, slicers, filters, interactions, drillthrough etc.)
25:34 - The second part of the video
💡 More Power BI:
===============
💥 Advanced Power BI Course - chandoo.org/wp/power-bi-course
Learn Power BI with me and master the technology.
Getting started with Power BI - youtu.be/9tF1IrfLflg
Power BI end-to-end project - youtu.be/uwe8C7K8fXY
How to use Power Query - youtu.be/MMdcczmULrU
How to use Power Pivot - youtu.be/PQ5lALdFHCU
What is Fabric - youtu.be/--cK4A2djoA
20+ Data & BI Terms explained - youtu.be/a906b80lCE8
For more info:
===========
📃 my website chandoo.org/wp
💻 my course chandoo.org/wp/power-bi-course
🤝 my linkedin linkedin.com/in/purnaduggirala
📷 my insta instagram.com/chandoo.xlsx
📧 get in touch chandoo.org/wp/contact
~
#powerbi #jargon #chandoo
📁 Download files 👉 chandoo.org/wp/free-call-centre-dashboard
In our final lesson of Excel module, we will be building a call centre performance dashboard as show-cased above. In this portfolio project, you will learn:
▶ Designing the dashboard color theme and fonts
▶ How to use Data model feature of Excel to connect datasets
▶ Creating KPIs and measures using Power Pivot and DAX
▶ Using Pivot tables to create all the dashboard calculations
▶ Making interactive Pivot Charts with slicers
▶ Applying conditional formatting to charts & tables
▶ Using XLOOKUP and images in Excel to show dynamic pictures
⏱ Video timestamps:
==================
0:00 - Demo of what we are going to build - call centre dashboard
1:35 - A closer look at the dataset and dashboard assets
2:57 - Set up dashboard color scheme and fonts
4:31 - Creating pivot tables
5:31 - Setting up the data model and relationships
7:29 - Calculating the DAX measures for various KPIs
12:47 - Making dashboard tiles
20:48 - Calls trend line graph
27:22 - Weekday trend bar graph
31:18 - Calls and amounts by rep (dynamic chart)
44:58 - Formatting slicers with styles
47:36 - Calls by customer demographics
50:19 - Rating distribution histogram
52:33 - Customer Revenue Analysis (table)
57:10 - Conditional formatting databars
58:51 - Highlighting selected "rep" in the table
1:01:34 - Adding the "picture" of selected rep
1:10:11 - Making dashboard title etc.
1:11:30 - How to learn more and become really good in Excel
📁 Files:
=======
📃 Blank data file: chandoo.org/wp/wp-content/uploads/2024/09/sample-data-excel-portfolio-project.xlsx
📊 Completed workbook (+more): chandoo.org/wp/free-data-analyst-course
🎶 Full course playlist: youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
💻 Full course webpage: chandoo.org/wp/free-data-analyst-course
👩💻 Advanced Excel course: chandoo.org/wp/excel-school-program
~
#Excel #Chandoo #fdac #freedataanalystcourse
While Excel formulas are powerful, you also need to plan and think hard before writing them to solve business problems. This is why I love Pivot tables. They are really easy to make and provide the biggest bang for buck. We can use Pivots to analyze data and answer business questions effectively and elegantly in short amount of time.
In this lesson, learn how to:
◉ Create a pivot table from your data
◉ Using the fields panel to construct your "dream" pivot
◉ Work with report filters, top 10 filters and sort options
◉ Change calculation in the pivot
◉ Set up slicers to "interact" with pivot tables
◉ Create "groups" in pivots
◉ Make pivot charts to analyze trends
◉ Calculate Cumulative "running" totals in Pivots
◉ Use conditional formatting with Pivots
◉ Percentage analysis with Pivots
◉ Set up advanced pivot tables to solve business problems
📁 Sample files (blank and completed workbooks):
==========================================
Blank data file
chandoo.org/wp/wp-content/uploads/2024/09/sample-data-pivots-blank.xlsx
Completed file + all other lessons of my free data analyst course
chandoo.org/wp/free-data-analyst-course
⏱ Video Topics & Timestamps:
==========================
0:00 - Introduction to the data and 10 problems
0:51 - Total calls by customer pivot
5:16 - Customer Satisfaction pivot
7:34 - Top 10 customers with value filters
11:19 - Using Report Filters to see top 10 customers for a specific representative
12:45 - Slicers are better than Report Filters
15:01 - Call duration analysis (grouping in Pivots)
20:32 - Trend analysis with Pivots (using dates)
24:40 - Year to Date Totals (cumulative or running totals in Pivots)
27:50 - YTD totals with financial year start in July
31:43 - Which days are busiest (conditional formatting with Pivots)
36:12 - Link between call duration and satisfaction (2-way pivots)
44:49 - Should we hire extra stuff in any months?
47:25 - Home work problems to practice Pivot Tables
48:30 - Coming up next in the free data analysis course
👩💻 Learn more:
============
💥 Check out my advanced Excel course to up your skills - chandoo.org/wp/excel-school-program
Why I stopped using Pivots - youtu.be/SPFQX82X03Q (1.3 mn views)
Don't make these pivot table mistakes - youtu.be/6A3e3s9YBgo
Learn Pivot Tables in 10 minutes - youtu.be/aofsdpjvK7w
How to make pivot reports when you have too much data - youtu.be/u4aeZ4ukrqI
FREE Data Analyst Course (playlist) - youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
~
#freedataanalystcourse #fdac #chandoo #excel #pivot
In this video:
==========
0:00 - What is CoPilot for Excel and how to enable it?
1:30 - How to use CoPilot to clean your data?
3:13 - Using CoPilot to write formulas
4:48 - CoPilot and natural language queries
6:58 - Complex analysis scenario with CoPilot
8:52 - Making Pivots with CoPilot
10:12 - Tips & Tricks of using CoPilot
11:16 - Asking CoPilot to find insights on its own
13:04 - Alternatives to CoPilot in Excel
🕸 CoPilot Requirements:
====================
learn.microsoft.com/en-us/copilot/microsoft-365/microsoft-365-copilot-requirements
📁 Sample file (to play with CoPilot):
==============================
Download this file and use it to understand how CoPilot works.
chandoo.org/wp/wp-content/uploads/2024/09/copilot-howto.xlsx
🤖 More AI videos:
================
10x your productivity with these AI tools in Excel - youtu.be/xGqH__hLVcU
ChatGPT made this Dashboard for me - youtu.be/mp18miYmmJY
How to use ChatGPT to improve your Excel skills - youtu.be/EsT5TNwbBVM
AI + Data Playlist - youtube.com/playlist?list=PLmejDGrsgFyBh9sO5sD_PY9nZblKjfFX5
~
I asked CoPilot "How to fly better?"
It just made a drawing of a butterfly 🦋
We are not talking any more 🤜💥🤛
#copilot #excel
~
Here is something that most people get wrong when learning Excel. They learn the functions, but not "when" and "how" to use them. So in this masterclass, let me tell you "when" and "how" to use Excel functions with 10 business problems.
What you will learn in this video:
◉ Working with Excel formulas & functions
◉ COUNTIFS, SUMIFS, AVERAGEIFS
◉ FILTER Functions, multiple criteria
◉ CHOOSECOLS function
◉ MIN, MAX
◉ SMALL, LARGE
◉ SORT + TAKE functions
◉ UNIQUE
◉ # OPERATOR
◉ COUNTA vs. COUNT functions
◉ VLOOKUP
◉ XLOOKUP
◉ INDEX@Mat+ MATCH
◉ IFERROR
◉ How to nest formulas to get the result you want
◉ Complex business problems and formula solutions for them
❤ And plenty of homework problems
📁 Sample files:
Please visit chandoo.org/wp/free-data-analyst-course for the sample file and more information about these functions.
⏱ Video timestamps:
0:00 - The problem with learning Excel functions
1:51 - Total salary and headcounts by department (COUNTIFS, SUMIFS, AVERAGEIFS)
8:28 - All employees with more than $100k salary (FILTER)
12:21 - Seeing only a few columns with FILTER using CHOOSECOLS
16:17 - How to learn & remember all this info
20:36 - All female employees with more than $100k salary (FILTER multiple conditions)
24:16 - Lowest, highest, top 5 salary values (MIN, MAX, MINIFS, MAXIFS, SMALL, LARGE, SORT+TAKE)
33:44 - Listing all departments with UNIQUE and using # operator
37:03 - Lookup functions (VLOOKUP, XLOOKUP, INDEX+MATCH, IFERROR)
53:22 - Highest paid person (XLOOKUP + MAX)
57:22 - All the employees that joined in March (FILTER + DATE FUNCTIONS)
1:01:41 - Making a report with formulas (COUNTIFS, SUMIFS, UNIQUE, #, Conditional Formatting)
1:09:09 - What's next?
📺 Learn more:
💼 Advanced Excel Course by Chandoo - chandoo.org/wp/excel-school-program
📺 Excel formula videos:
XLOOKUP - youtu.be/DDlZXXEI1bU
VLOOKUP - youtu.be/8rtvDQVQaA0
INDEX + MATCH - youtu.be/kly0uPIM4IU
FILTER - youtu.be/JuTdj2j-9Kg
Dynamic Arrays (# operator) - youtu.be/kR6czKY4FEw
SUMIFS - youtu.be/YEt-aYbDTrs
IF (Advanced uses) - youtu.be/-yFpzIRifK4
5 EVEN MORE advanced IF examples - youtu.be/15NtFld9mVM
Top 15 Excel functions - youtu.be/B5hayFelHDU
Top 10 formulas for accountants - youtu.be/xarUaqsjmFY
🎶 Free Data Analyst Course Playlist:
youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
~
#excel #dataanalyst #chandoo #freedataanalystcourse #fdac
This is "Essential" Excel in 40 minutes.
What you will learn:
1) How to use data in Excel
2) The importance of Excel tables
3) Working with Filters & Slicers in Excel to do quick analysis
4) Using "Total Row" / SUBTOTAL function
5) Understanding Excel cell references - Relative / Absolute / Structural / Spill
6) Important and useful Excel functions - IF, IFS, SUBTOTAL
7) Creating and using Pivot Tables
8) Making Excel Charts - Box plots to explore salary distribution
9) Interactive charts with slicers
10) Next steps to improve your understanding of Excel
📁 Sample File & Course Page:
=========================
Visit chandoo.org/wp/free-data-analyst-course for the sample file and course details
Blank Workbook (for practice) - chandoo.org/wp/wp-content/uploads/2024/08/essential-excel-concepts-blank.xlsx
Completed file (with all examples) - chandoo.org/wp/free-data-analyst-course
⌚ Video Topics:
==============
0:00 - What are the Essential Excel topics?
0:41 - About the dataset and the 5 business problems
2:22 - The importance and how to use Excel Tables
8:45 - Using Total Row & SUBTOTAL functions
12:35 - Cell References in Excel - Relative, Absolute, Spill
20:52 - Spill Ranges and Dynamic Array Formulas
22:44 - Using IF / IFS functions in Excel (bonus calculation example)
31:35 - Excel Pivot Tables (and making a department-wise report)
38:35 - Using Excel Charts (Box plots for salary distribution)
📺 Learn more:
=============
💥 Paid Courses for Data People:
Advanced Excel Course - (30+ hours of video, 10k+ happy students)
chandoo.org/wp/excel-school-program
Advanced Power BI Course - (14+ hours of video, 2k+ happy students)
chandoo.org/wp/power-bi-course
🎶 Free Data Analyst Course Playlist:
📚 Course page and all lessons: chandoo.org/wp/free-data-analyst-course
📺 YouTube Playlist: youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
👩💻 Related Videos:
Dynamic Arrays + Spill range in Excel: youtu.be/kR6czKY4FEw
Excel Basics for a Beginner: youtu.be/F7aPazuS8QY
Excel beginner to PRO course: youtu.be/v2oNWja7M2E
Important Excel functions: youtu.be/B5hayFelHDU
~
#excel #dataanalyst #chandoo #freedataanalystcourse #fdac
Sample file: chandoo.org/dac
~
In this video, you will learn:
Why Power Query is the best feature in Excel and Power BI
Webscraping with Power Query
Understanding the PQ Editor Screen
Data cleaning with Power Query editor
Fill down operation (dealing with nulls)
Adding columns
Filtering the data
Find / replace values
Trimming data
Column Quality Indicators
Adding column based on rules (conditional column)
Fixing date format issues
Splitting First and Last name
Writing M code to create columns
Calculating tenure from start date
Updating and refreshing the data
📁 Sample File
=============
Web scraping: en.wikipedia.org/wiki/2020_Summer_Olympics_medal_table
Staff data file: chandoo.org/wp/wp-content/uploads/2024/08/sample-staff-data.xlsx
Completed file: chandoo.org/dac
Free Data Analyst course: chandoo.org/dac
⏱ Video topics:
=============
0:00 - Why Power Query is important?
1:46 - Web scraping with Power Query
4:54 - Understanding the Power Query Editor (PQE)
7:33 - Data cleaning the web scraped data
13:36 - Loading the data to Excel
15:43 - Business Data with Power Query
17:50 - Data cleaning staff data
24:55 - Cleaning "dates" with Power Query
26:15 - Splitting first and last name
28:25 - Bucketing salary values
30:25 - Calculating the "tenure" of the employee
31:54 - Writing M code yourself - simple example
33:35 - Closing and loading the data (and refresh process)
📺 More videos - FREE Data Analyst Course:
=====================================
Course page: chandoo.org/dac
Check out the playlist - youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
🤖 More Power Query
==================
Power Query masterclass (90 mins) - youtu.be/PiFAa_jjaEI
Power Query in 15 minutes - youtu.be/UAFExySaSPY
Power Query and automation (2 hours) - youtube.com/live/t43kb7BRsFY?feature=share
Power Query mini-course - chandoo.org/wp/power-query-power-course
Power Query + Advanced Excel Course - chandoo.org/wp/excel-school-program
~
#powerquery #chandoo #freedataanalystcourse #fdac
💥 Course page 👉 chandoo.org/dac
You will be learning:
How to use WHERE clause in SQL
Work with AND / OR / LIKE / IN operators in SQL
Getting DISTINCT values with SQL
Grouping data with GROUP BY
Using SQL JOINS
Creating Common Table Expressions (CTE)
Business Analysis and Decision Making with SQL
For the purpose of this video, we will be using the Sakila sample database of MySQL. I explain the process of installing MySQL and the sample databases in the previous video of this series here -
youtu.be/Iceaqdy7mEs
The queries we will be developing are:
# 1) All films with PG-13 films with rental rate of 2.99 or lower
# 2) All films that have deleted scenes
# 3) All active customers
# 4) Names of customers who rented a movie on 26th July 2005
# 5) Distinct names of customers who rented a movie on 26th July 2005
# 6) How many rentals we do on each day?
# 7) All Sci-fi films in our catalogue
# 8) Customers and how many movies they rented from us so far?
# 9) Which movies should we discontinue from our catalogue (less than 2 lifetime rentals)
# 10) Which movies are not returned yet?
# H1) How many distinct last names we have in the data?
# H2) How much money and rentals we make for Store 1 by day?
# What are the three top earning days so far?
~
⏱ VIDEO TOPICS:
===============
0:00 - Introduction to Advanced SQL
0:48 - How to understand the structure of any dataset? (ERD)
4:30 - Primary vs. Foreign Key
7:30 - Let's write SQL queries
7:50 - Query 1
11:32 - Query 2
13:37 - Query 3
14:28 - Homework
14:43 - Query 4
19:32 - Bonus Query
20:34 - Query 5
21:43 - Homework
22:04 - Query 6
24:42 - Query 7
29:15 - Query 8
32:10 - Query 9
35:36 - How to use CTEs (Common Table Expressions)
38:37 - Query 10
42:06 - Homework
42:45 - Limitations of SQL
📁 Sample data and completed queries:
=================================
For all sample files, completed queries and homework solutions, visit the page below:
chandoo.org/dac
💥 ADVANCED DATA COURSES BY CHANDOO
======================================
If you want to learn advanced Excel and / or Power BI, consider my paid courses. These in-depth courses cover the entire spectrum of Excel, Power BI, Power Query, Power Pivot and Dashboard reporting in one place with plenty of real-world examples, portfolio projects and completion certificate.
Check out these links:
Advanced Excel Course - chandoo.org/wp/excel-school-program
Advanced Power BI Course - chandoo.org/wp/power-bi-course
~
#sql #database #chandoo #freedataanalystcourse #fdac
💥Important Links:
================
Get the materials and instructions here - chandoo.org/dac
Install MySQL - dev.mysql.com/downloads/installer
Sakila Database information - dev.mysql.com/doc/sakila/en/sakila-structure.html
Advanced Excel Course - chandoo.org/wp/excel-school-program
Power BI Course - chandoo.org/wp/power-bi-course
🤔What you will learn:
==================
What is a database?
What is SQL
How to install MySQL on your computer
How to write SQL queries
Using the sample database - Sakila
⏱️topics in this video:
==================
0:00 - What you are going to learn in this video?
0:17 - What is a database?
1:55 - What is SQL?
3:26 - Key Database Terminology and how databases look
6:42 - Types of database software
8:21 - How to install MySQL and sample datasets (Sakila) on your computer?
10:15 - Using MySQL workbench
12:30 - Understanding the Sakila Sample Database
14:28 - Writing your first SQL queries
17:40 - Using column names, where clause in SQL
19:30 - Using ORDER BY clause
20:30 - Solving a work problem with SQL (all rentals in the last week)
24:03 - What is coming up in the lesson #2
📺 Watch next:
=============
Lesson 2 - Advanced SQL Concepts + 10 queries: Coming on 6-August
Free Data Analyst Course Playlist: youtube.com/playlist?list=PLmejDGrsgFyCD25L2XEKESQEw8smsY6WO
More on SQL:
===========
Learn SQL in an hour: youtu.be/l8DCPaHc5TQ
SQL basics: youtu.be/yfWcZ6eot4g
SQL in an hour - article: chandoo.org/wp/learn-sql-for-data-analysis
~
#sql #database #chandoo #freedataanalystcourse #fdac


