Power Bi

Perfect 👍 We’ll go step by step through the full course outline you uploaded. I’ll cover one topic
at a time with:

• Definition / Concept
• Steps to apply in Power BI
• Example (same style as in the video captions)

Let’s start with Topic 1.

1 Power BI 2024 Course Introduction

1️⃣
🔹 Definition / Concept
Power BI is Microsoft’s Business Intelligence (BI) tool used to convert raw data into interactive
visual reports and dashboards. It enables organizations to make better decisions using data.

It is essential for roles such as:

• Data Analyst
• Business Analyst
• Product Analyst
• Marketing Analyst

Why it is important?

• Power BI dominates the BI market with ~30% more share than other tools like Tableau
or Qlik.
• Demand for Power BI developers has increased by 15%+ in the job market.
• Similar to Excel, so easy to learn if you already know Excel.
• Provides seamless data connectors, powerful visuals, and cloud-based collaboration.

🔹 Steps to Apply in Power BI

When working with Power BI, the workflow is usually:

1. Connect Data → Bring in data from Excel, CSV, SQL, or cloud.

2. Transform Data → Clean and prepare data using Power Query Editor.
3. Model Data → Create relationships, define cardinality, and build measures.
4. Visualize → Build charts, KPIs, dashboards.
5. Share → Publish to Power BI Service for collaboration.
🔹 Example (from video)
Suppose a company has Sales Data stored in Excel.

• Step 1: Import Sales.xlsx into Power BI.

• Step 2: Use Power Query to clean (remove null values, rename columns).
• Step 3: Build relationships between Orders and Customers tables.
• Step 4: Create visuals like bar charts for “Sales by Month” or “Top 5 Products.”
• Step 5: Share report with management to help in decision-making.

✅ This was Topic 1. Do you want me to move to Topic 2: Introduction to Power BI now?

Great 👍 Let’s move to Topic 2.

2️⃣Introduction to Power BI
🔹 Definition / Concept
Power BI is a Business Intelligence (BI) platform by Microsoft that helps you:

1. Connect to multiple data sources (Excel, SQL Server, CSV, Cloud, APIs).
2. Transform & Clean the data using Power Query Editor.
3. Model the data by creating relationships and hierarchies.
4. Visualize the data using charts, tables, KPIs, and dashboards.
5. Share & Collaborate by publishing reports to Power BI Service.

👉 In short: “Connect → Transform → Model → Visualize → Share.”

🔹 Steps to Apply in Power BI

1. Open Power BI Desktop.

2. On the Home tab, click Get Data → Choose your source (Excel, CSV, SQL, etc.).

3. Transform Data (opens Power Query) → Clean & prepare data.

4. Once data is loaded, go to:

– Report View → to build charts/visuals.
– Data View → to see tables and columns.
 – Model View → to build relationships between tables.
5. Use Visualizations Pane to add bar charts, pie charts, KPIs, maps, etc.

6. Save report as .pbix file or publish to Power BI Service.

🔹 Example (from video)

Imagine you are analyzing a Pizza Sales Dataset:

• Import Orders.csv and Pizza_Types.csv into Power BI.

• In Power Query, clean column names (e.g., “col1” → “Order ID”).
• In Model View, create a relationship between Orders[Pizza_ID] →
Pizza_Types[Pizza_ID].
• In Report View, create a bar chart showing “Total Orders by Pizza Type.”
• Publish the dashboard so managers can track which pizza sells the most.

✅ That’s Topic 2 completed. Do you want me to continue with Topic 3: Installation of Power BI
Desktop?

Perfect 👍 Let’s move to Topic 3: Installation of Power BI Desktop.

3️⃣Installation of Power BI Desktop

🔹 Definition / Concept
Power BI Desktop is a free application from Microsoft that allows you to:

• Import and transform data

• Build models and relationships
• Create interactive dashboards
• Save reports locally (.pbix file) or publish them to the Power BI Service

👉 It is the main development tool for creating reports before sharing them online.

🔹 Steps to Install Power BI Desktop

1. Open your browser → Search for:

Power BI Desktop Download

 2. Click the official Microsoft link (not third-party).

3. Download the installer (available via Microsoft Store or direct .exe file).

4. Run the installer and complete the setup.

5. After installation → Search Power BI Desktop in your Start Menu.

6. Open it → You’ll see the Welcome Screen with:

– Recent Reports
– “Get Data” option
– “New Report” option

🔹 Interface Overview
Once Power BI Desktop opens, you’ll find:

• Home Tab → Get Data, Manage Queries, Enter Data

• Report View (left pane, chart icon) → To build dashboards
• Data View (table icon) → To see rows & columns
• Model View (relationship icon) → To manage relationships
• Visualizations Pane (right side) → Charts, cards, KPIs, slicers
• Fields Pane (right side) → Your imported data tables

🔹 Example (from video)

• You install Power BI Desktop → Open it → Click Get Data → Excel.

• Import Orders.xlsx.

• You’ll see 3 views:

– Data View → Table of Orders
– Model View → Orders table automatically connected with Customers table
– Report View → Where you can create bar charts like “Sales by Customer.”

👉 The instructor also explained that Power BI automatically detects relationships (one-to-one,
one-to-many, many-to-one).

✅ That’s Topic 3 completed. Shall I continue with Topic 4: Types of Data Connectors in Power
BI?

Got it 👍 Kuldeep. From Topic 4 onward, I’ll explain in much more detail with:
 • Full concept explanation
• Step-by-step process in Power BI
• Detailed examples (same way the instructor showed in video with Pizza Sales / Classic
Models dataset).

Let’s start with Topic 4: Types of Data Connectors in Power BI.

4️⃣Types of Data Connectors in Power BI

🔹 Definition / Concept
A Data Connector in Power BI is like a “bridge” that allows you to bring data from different
sources (files, databases, cloud, or services) into Power BI.

👉 Without connectors, you cannot load or analyze data in Power BI.

Power BI supports 100+ connectors, which can be divided into the following categories:

1. File-based connectors
– Excel (.xlsx)
– CSV (.csv)
– XML (.xml)
– JSON (.json)
– PDF (.pdf)
– Folder (all files inside a folder can be combined)
2. Database connectors
– SQL Server
– MySQL
– PostgreSQL
– Oracle
– IBM DB2
– Snowflake
3. Cloud-based connectors
– Azure (Blob, Data Lake, SQL Database, Synapse)
– Google BigQuery
– Amazon Redshift
4. Online services connectors
– SharePoint Online
– Salesforce
– Dynamics 365
 – GitHub
– Adobe Analytics
5. Other connectors
– ODBC (Generic database connection)
– Web API (Connect to REST APIs)
– Blank Query (custom connections using M code)

🔹 Steps to Connect Data in Power BI

1. Open Power BI Desktop.

2. Go to Home → Get Data.

3. Select the type of data source:

– Excel → Browse and open a .xlsx file.
– SQL Server → Enter server name & database.
– Folder → Select folder location → combine all files.
– Web API → Enter URL endpoint.
4. Preview Window will appear → choose which tables/sheets you want.

5. Click Load (direct load) or Transform Data (to clean before loading).

🔹 Example 1: File-based Connection (Excel)

Suppose you have Orders.xlsx containing order details.

• Go to Home → Get Data → Excel → Browse file.

• Select the sheet (Orders).
• Preview → Click Load.
• Now the Orders table is visible in Fields pane.

🔹 Example 2: Folder Connection

In the video, the instructor used a Pizza Sales Dataset where multiple CSV files were stored in a
folder:

• Orders.csv
• Order_Details.csv
• Pizzas.csv
• Pizza_Types.csv

Steps:

• Go to Get Data → Folder.

 • Select the folder path (e.g., Desktop/PizzaSales).
• Power BI lists all files inside.
• Instead of loading them one by one, click Combine & Load.
• All CSV files are imported into Power BI as tables.

👉 Advantage: If a new CSV file is added to the folder, Power BI automatically refreshes and loads
it.

🔹 Example 3: Database Connection (SQL Server)

Suppose you want to connect to an SQL Server:

• Go to Get Data → SQL Server.

• Enter Server Name (e.g., localhost or company server).
• Enter Database Name (optional, you can choose later).
• Choose Import (load all data) or DirectQuery (query data live from the database).
• Preview tables → Load.

👉 Used when working with large enterprise datasets stored in SQL.

🔹 Example 4: Online Service Connection (SharePoint)

Suppose your company stores files in SharePoint Online.

• Go to Get Data → SharePoint Online Folder.

• Enter your SharePoint site URL.
• Authenticate with Office 365 credentials.
• Select files → Load into Power BI.

🔹 Example 5: Web API Connection

Suppose you want to fetch live COVID-19 data from a government API.

• Go to Get Data → Web.

• Paste API URL (e.g., https://api.covid19api.com/summary).
• Power BI fetches JSON data.
• Transform JSON → Tables → Load into Power BI.

🔹 Key Points to Remember

• Always decide between Load (direct import) vs Transform (clean first).
• Use Folder Connector when you have multiple files of the same type.
• Use DirectQuery for large databases (to avoid loading millions of rows locally).
 • Use Import Mode when datasets are small and you want faster analysis.

✅ This was Topic 4: Types of Data Connectors in Power BI (detailed).

Do you want me to continue with Topic 5: Basic Transformations in Power BI?

Awesome 🚀 Let’s move to Topic 5: Basic Transformations in Power BI. This is one of the most
important parts because without clean data, no dashboard will make sense.

5️⃣Basic Transformations in Power BI

🔹 Definition / Concept
Data is rarely clean when imported. It usually has null values, duplicate rows, inconsistent
formats, or unwanted columns. 👉 Transformation means cleaning and reshaping data so that
it’s ready for analysis.

Power BI uses Power Query Editor for all transformations.

• It works on M language (behind the scenes).

• Every step you apply is recorded (can be modified/removed anytime).

🔹 Steps to Apply Basic Transformations

1. Open Power BI Desktop → Import data (Excel/CSV/Database).

2. On Navigator window → click Transform Data instead of Load.

3. This opens Power Query Editor.

4. Common transformations you can apply:

– Use First Row as Headers → Convert first row into column names.
– Rename Columns → Right-click column → Rename.
– Remove Columns → Delete unnecessary fields.
– Remove Duplicates → Home → Remove Rows → Remove Duplicates.
– Replace Values → Right-click column → Replace (e.g., "M" → "Male").
– Change Data Types → From Text → Date/Number/Decimal.
– Split Columns → Example: Split “Full Name” into First Name and Last Name.
– Merge Columns → Combine two columns (e.g., City + State).
🔹 Example (Pizza Sales Dataset from video)
The dataset had 4 CSV files:

• Orders
• Order_Details
• Pizzas
• Pizza_Types

👉 Transformations Applied:

1. Use First Row as Headers

– Orders.csv file had column names in the first row.
– Applied → Home → Use First Row as Headers.
2. Rename Columns
– “col1” → “Order ID”, “col2” → “Customer Name”, etc.
3. Remove Unnecessary Columns
– Removed columns like “Temp Field” that were not useful for analysis.
4. Split Column
– In Customer Name field (“John Smith”), applied Split by Delimiter → Space.
– Result → First Name = John, Last Name = Smith.
5. Merge Columns
– Combined City + State → Created a new “Location” column.
6. Change Data Types
– Order Date column → Converted from Text to Date.
– Price column → Converted from Text to Decimal Number.

🔹 Why Transformations are Important?

• Without transformations, reports will show incorrect results.
• Example: If "Date" is in Text format → Charts won’t show proper monthly trends.
• Example: Null values in "Sales Amount" → Wrong Total Sales calculation.

🔹 Additional Notes from Instructor

• Power BI records every step in Applied Steps Pane (right-hand side).
• You can undo/modify/delete steps anytime.
• Transformations don’t change the original dataset; they only affect how Power BI reads
it.

✅ That’s Topic 5 completed: Basic Transformations in Power BI (detailed).

Shall I continue with Topic 6: Dealing with Text Tools in Power BI?

Perfect 👍 Let’s move to Topic 6: Dealing with Text Tools in Power BI.

6️⃣Dealing with Text Tools in Power BI

🔹 Definition / Concept
Text Tools in Power BI are used when working with string (text) data such as names, categories,
product codes, etc.

👉 In real datasets, text often needs cleaning, formatting, or restructuring before analysis. For
example: splitting full names, changing case, trimming spaces, extracting substrings.

All these operations are done in Power Query Editor.

🔹 Steps to Use Text Tools

1. Open Power Query Editor → Select a text column.
2. On the top ribbon, go to Transform Tab → Text Column Tools.
3. Apply any of the following options:

1. Format → UPPERCASE, lowercase, Capitalize Each Word

• Converts text to a consistent case.

• Example:
– "john smith" → "John Smith"
– "pizza" → "PIZZA"

2. Trim / Clean
• Trim → Removes extra spaces from beginning and end.
• Clean → Removes invisible/unwanted characters.
• Example: " Pizza " → "Pizza".

3. Split Column
• Split text into multiple columns based on delimiter.
• Example: “John Smith” → Split by space → “John” | “Smith”.
• Example: “NY-1001” → Split by hyphen → “NY” | “1001”.

4. Extract → First Characters, Last Characters, Range, or Digit

• Example: "PZ-2024" → Extract first 2 chars → "PZ".
 • Example: "Pizza123" → Extract digits → "123".

5. Replace Values
• Replace specific text with another value.
• Example: Replace “Veggie Pizza” → “Vegetarian Pizza”.

6. Merge Columns
• Combine two or more text columns into one.
• Example: City = “New York”, State = “NY” → Merge → “New York, NY”.

🔹 Example (from Pizza Sales Dataset in video)

• Customer Name field:
– Split by Space → Created First Name and Last Name.
• Pizza Type ID (e.g., “v_pizza_margherita”):
– Used Replace Values → Replaced underscores with spaces → “v pizza margherita”.
– Then applied Capitalize Each Word → “V Pizza Margherita”.
• Location Column:
– City + State merged together using Merge Columns → “Chicago, Illinois”.
• Order Notes Column:
– Applied Trim + Clean → Removed extra spaces and special characters.

🔹 Why Text Tools are Important?

• Helps maintain data consistency (e.g., “NY” vs “New York” problem).
• Makes dashboards look professional with clean labels.
• Avoids errors in grouping/aggregations caused by text mismatches.

✅ That’s Topic 6 completed: Dealing with Text Tools in Power BI.

Shall I continue with Topic 7: Dealing with Unwanted Columns and Null Values?

Great 👍 Let’s move to Topic 7: Dealing with Unwanted Columns and Null Values in Power BI.
7️⃣Dealing with Unwanted Columns and Null
Values in Power BI
🔹 Definition / Concept
In real-world datasets, you often encounter:

• Unwanted columns → Fields that don’t contribute to analysis (e.g., Temporary IDs,
Metadata, Notes).
• Null / Missing values → Empty cells that may lead to wrong calculations (e.g., missing
Sales Amount).

👉 Cleaning these is a critical step in ETL (Extract, Transform, Load) because they directly affect
the quality of reports and dashboards.

Power BI provides tools in Power Query Editor to handle them.

🔹 Steps to Remove Unwanted Columns

1. Open dataset in Power Query Editor.
2. Select the column(s) you don’t need.
3. Right-click → Remove OR go to Home Tab → Remove Columns.
4. Alternatively, keep only the required columns → Home → Choose Columns → Select
Needed Columns.
5. Power BI records this step in Applied Steps Pane so you can undo/modify it later.

Example:

• In the Pizza Sales dataset, columns like “Temp_Notes” or “Unnamed Column” were
removed.
• Only essential columns like Order_ID, Date, Pizza_Type, Quantity, Price were kept.

🔹 Steps to Handle Null Values

1. In Power Query Editor, select the column with nulls.

2. Options to handle nulls:

– Remove Rows → Remove Blank Rows → Deletes rows where key fields are
empty.
– Replace Values → Replace nulls with a default value (e.g., 0, “Unknown”).
– Fill Up/Down → Fill null cells with nearby values.
– Conditional Column → Create logic (e.g., if null then “Not Available”).
🔹 Example (from Pizza Sales Dataset)
• Customer Name: Some rows had missing values → Replaced null with “Guest”.
• Order Date: Rows with completely null dates were removed.
• Quantity: Null replaced with “0” because blank meant “no order placed.”
• Pizza Size: Used Replace Values → Null → “Regular”.

🔹 Why This Is Important?

• Unwanted Columns:
– Reduce report performance (extra data slows down refresh).
– Create confusion for end-users.
• Null Values:
– Wrong aggregations (e.g., Total Sales excludes null rows).
– Incorrect visuals (charts may show blank categories).
– Poor data storytelling (missing values break trends).

Best Practice: Always remove unnecessary fields and treat nulls before modeling.

✅ That’s Topic 7 completed: Dealing with Unwanted Columns and Null Values in Power BI.

Do you want me to continue with Topic 8: Dealing with Numerical Tools in Power BI?

Perfect 👍 Now let’s move to Topic 8: Dealing with Numerical Tools in Power BI.

8️⃣Dealing with Numerical Tools in Power BI

🔹 Definition / Concept
Numerical tools in Power BI are used to handle number-based columns like Quantity, Sales,
Price, Discount, Profit, etc.

👉 They allow us to:

• Perform basic calculations (sum, average, min, max).

• Perform statistical operations (standard deviation, variance).
• Create custom columns for new calculations.
• Format numbers for reporting (currency, percentages, decimals).
These tools are mostly applied in Power Query Editor (data cleaning stage) and later in Report
View using DAX.

🔹 Steps to Use Numerical Tools in Power Query Editor

1. Open Power Query Editor → Select a numeric column.

2. On the Transform Tab → Statistics, you’ll see options:

– Sum → Adds all values.
– Average → Mean of values.
– Median → Middle value.
– Min/Max → Lowest and highest value.
– Standard Deviation → Spread of data.
– Count → Number of rows.
3. On the Transform Tab → Number Column Tools, you can:
– Round Up / Round Down
– Absolute Value (convert negative numbers to positive)
– Modulo (find remainder)
– Add/Subtract/Multiply/Divide by constant
4. On the Add Column Tab, you can create new calculated columns:
– Example: Total Amount = Quantity × Price

🔹 Example (from Pizza Sales Dataset)

• Quantity Column:
– Some rows had decimal values (e.g., 1.5) → Rounded to nearest integer using
Round Function.
• Price Column:
– Converted from Text → Decimal.
– Applied Currency Format ($).
• Total Sales Column:
– Created new column → Total Sales = Quantity × Price.
– Example: 2 × 250 = 500.
• Discount Column:
– If missing, replaced with 0.
– Used formula: Final Amount = Total Sales – Discount.
🔹 Steps to Use Numerical Tools in Report View (DAX & Formatting)
1. Go to Model View → Select a numeric field.
2. Change Data Type → Whole Number, Decimal, Currency.
3. Change Format → 2 decimal places, percentage, currency.
4. Use Quick Measures for calculations like % of Total, Running Total, Year-to-Date.

🔹 Why Numerical Tools are Important?

• Clean & correct numerical data ensures accurate KPIs.

• Helps build dashboards like:

– Total Sales by Month
– Top 5 Products by Revenue
– Profit % per Pizza Type
• Prevents errors (e.g., if Sales is Text, visuals won’t work).

✅ That’s Topic 8 completed: Dealing with Numerical Tools in Power BI.

Do you want me to continue with Topic 9: Dealing with Date and Time in Power BI?

Great 👍 Let’s dive into Topic 9: Dealing with Date and Time in Power BI. This is a very important
part because time-based analysis (daily, monthly, yearly trends) is one of the most common
requirements in dashboards.

9️⃣Dealing with Date and Time in Power BI

🔹 Definition / Concept
Date and Time columns allow us to analyze data across time periods.

👉 Examples of use:

• Sales by Month / Year

• Daily Order Trends
• Customer Lifetime Value
• Comparing Current Month vs Previous Month

Power BI provides powerful tools in Power Query Editor and DAX to handle Date & Time.
🔹 Steps to Work with Date/Time Columns in Power Query
1. Change Data Type
– Select column → Home Tab → Data Type → Date, Time, or Date/Time.
– Example: Convert “01-02-2024 10:30:45” from Text → Date/Time.
2. Extract Date/Time Parts (Transform Tab → Date or Time Menu)
– Year / Month / Day / Quarter / Week
– Hour / Minute / Second
Example: From 2024-07-15 14:35:20 → Extract Year = 2024, Month = 07, Hour =
14.

3. Age & Difference

– Calculate difference between two dates (e.g., Order Date vs Delivery Date).
– Returns values in days.
4. Date Hierarchy (automatic in visuals)
– When you add a date field in visuals, Power BI creates a hierarchy: Year → Quarter
→ Month → Day.
5. Formatting Dates
– Example: Display “2024-07-15” as “15-Jul-2024”.
– Select column → Modeling Tab → Format → Choose Date format.

🔹 Example (Pizza Sales Dataset)

• Order Date Column (Text → Date)
– Converted to Date Data Type.
– Extracted Year, Month, Day for time-based analysis.
• Delivery Time column:
– Extracted Hour from DateTime → Showed peak order hours (e.g., 7 PM – 9 PM).
• Created New Column (Delivery Days):
– Formula: DeliveryDays = Delivery Date – Order Date.
– Example: If Order Date = 1 Jan 2024, Delivery Date = 3 Jan 2024 → DeliveryDays =
2.
• Sales Trend Analysis:
– Built a line chart with Order Date on X-axis and Total Sales on Y-axis.
– Used Power BI’s Date Hierarchy → Drilled down from Year → Month → Day.

🔹 DAX for Date and Time (used later in Report View)

1. TODAY() → Returns current date.
 – Example: = TODAY() → 02-Sep-2025.
2. NOW() → Returns current date & time.

3. DATEDIFF(StartDate, EndDate, Interval) → Finds difference.

– Example: DATEDIFF(OrderDate, DeliveryDate, DAY) → 2.
4. YEAR(), MONTH(), DAY() → Extract parts of a date.

5. EOMONTH(Date, Offset) → End of month date.

6. SAMEPERIODLASTYEAR(DateColumn) → Compare this year vs last year.

🔹 Why Date & Time Handling is Important?

• Time-based reporting is the heart of BI dashboards.

• Businesses track:
– Sales growth over time
– Monthly expenses
– Seasonal demand patterns
• Without proper Date formatting, visuals won’t work correctly (e.g., Power BI may
sort months alphabetically instead of chronologically).

✅ That’s Topic 9 completed: Dealing with Date and Time in Power BI.

Do you want me to continue with Topic 10: Adding Conditional Columns to Power BI?

Perfect 👍 Now let’s cover Topic 10: Adding Conditional Columns to Power BI.

🔟 Adding Conditional Columns to Power BI

🔹 Definition / Concept
A Conditional Column is used when you want to create a new column based on conditions
applied to existing data. 👉 Think of it like an IF-ELSE statement in Excel or programming.

For example:

• If Sales > 500 → “High Sales”

• Else → “Low Sales”
This is very useful for categorizing data into groups such as Profit Level, Customer Type, Order
Priority, etc.

🔹 Steps to Create a Conditional Column (Power Query Editor)

1. Import dataset → Open Power Query Editor.

2. Go to Add Column Tab → Conditional Column.

3. A dialog box will open → Define rules.

– Select Column → Apply condition (e.g., greater than, equal to).
– Specify the Value to check.
– Enter the Output (result when true).
– Optionally, add Else Output (result when false).
4. Click OK → New column is created with your conditions applied.

🔹 Example 1: Sales Categorization

• Dataset: Pizza Sales

• Existing Column: Total Sales

• Steps:
– Go to Add Column → Conditional Column.
– Rule: If [Total Sales] > 500 → Output = “High Sales”
– Else → “Low Sales”
• Result: A new column called Sales Category with values “High Sales” or “Low Sales.”

🔹 Example 2: Order Priority

• Existing Column: Delivery Days (calculated earlier).

• Rule:
– If [Delivery Days] ≤ 2 → “On Time”
– If [Delivery Days] > 2 and ≤ 5 → “Delayed”
– Else → “Very Late”
• This helps management see how fast orders are delivered.

🔹 Example 3: Pizza Size Classification

• Column: Pizza Size (S, M, L, XL).
 • Conditional Column Rule:
– If Size = “S” → “Small Pizza”
– If Size = “M” → “Medium Pizza”
– If Size = “L” → “Large Pizza”
– Else → “Extra Large Pizza”

🔹 Alternative Method (Using DAX in Report View)

You can also create conditional logic using DAX Calculated Columns.

Example:

Sales_Category =
IF(Orders[Total Sales] > 500, "High Sales", "Low Sales")

👉 Advantage: More flexibility and can use advanced conditions.

🔹 Why Conditional Columns are Important?

• Allow grouping and classification for better analysis.
• Makes dashboards more business-friendly (instead of just raw numbers).
• Helps managers quickly identify trends (e.g., High vs Low Sales, On-Time vs Delayed
Orders).

✅ That’s Topic 10 completed: Adding Conditional Columns to Power BI.

Do you want me to continue with Topic 11: Merge Queries and Append Queries in Power BI?

Perfect 👍 Let’s continue with Topic 12: Column Formats in Power BI.

1️⃣2️⃣Column Formats in Power BI

🔹 Definition / Concept
Column formatting in Power BI refers to defining how data is displayed and interpreted.

👉 Even if the data is correct, wrong formatting can cause:

• Errors in calculations
• Misleading visuals
 • Poor readability for end-users

Formatting ensures that numbers, dates, and text are properly represented in reports.

🔹 Types of Column Formats

1. Data Types (Defines the nature of the column)
– Whole Number → For counts (e.g., Quantity = 10).
– Decimal Number → For continuous values (e.g., Price = 199.99).
– Currency → For money values (e.g., ₹2500, $100).
– Date / Time → For order dates, delivery dates.
– Text → For names, categories.
– Boolean → True/False values.
2. Formatting Options (Controls how values are displayed)
– Decimal Places (0, 2, etc.)
– Thousand Separator (1,000 vs 1000)
– Currency Symbols (₹, $, €)
– Percentage Format (e.g., 0.25 → 25%)
– Date Format (DD-MM-YYYY, MMM-YYYY, etc.)
3. Categorical Formatting (Helps visuals interpret correctly)
– Mark column as Country, State, City, Postal Code (important for Map visuals).
– Mark column as URL (so links become clickable).
– Mark column as Image URL (to display logos or product images).

🔹 Steps to Change Column Format

1. Load dataset into Power BI Desktop.

2. Go to Data View (left sidebar).

3. Select a column → Go to Column Tools (top ribbon).

4. Choose:
– Data Type → Number, Text, Date, etc.
– Format → Currency, Decimal places, Percentage, Date Style.
5. (Optional) Rename column → Right-click → Rename.

🔹 Example (Pizza Sales Dataset)

• OrderDate → Changed from Text → Date format (DD-MMM-YYYY).
• Quantity → Whole Number (no decimals).
• Unit Price → Decimal Number formatted as Currency ($).
 • Total Sales → Currency, 2 decimal places.
• Discount → Percentage format (e.g., 0.1 → 10%).
• Pizza Type → Text format.
• Store Location → Marked as City for use in Map visuals.

🔹 Why Column Formatting is Important?

• Accuracy in Calculations: If numbers are stored as text, SUM won’t work.

• Readability: Managers prefer “$25,000.50” over “25000.5”.

• Correct Visualizations:
– Dates must be in Date format for time-based charts.
– Location must be correctly categorized for maps.
• Professional Reports: Well-formatted numbers/dates = polished dashboards.

✅ That’s Topic 12 completed: Column Formats in Power BI.

Do you want me to continue with Topic 13: Creating a Table Using Power BI?

Great 👍 Let’s move to Topic 13: Creating a Table Using Power BI.

1️⃣3️⃣Creating a Table Using Power BI

🔹 Definition / Concept
A Table in Power BI is a visual representation of data in rows and columns (just like Excel
tables).

• Tables are useful when you want to display detailed raw data instead of only
summaries.
• They are fully interactive → You can sort, filter, and apply conditional formatting.

👉 In Power BI, “Table” has two meanings:

1. Data Table (Model Level) → The actual dataset loaded into Power BI.
2. Table Visual (Report Level) → A visual you add to a dashboard to display rows &
columns.

Here, we’re focusing on Table Visual.

🔹 Steps to Create a Table Visual
1. Open Power BI Desktop → Go to Report View.

2. In the Visualizations Pane (right side) → Click on the Table icon.

3. A blank table visual will appear on the canvas.

4. Drag and drop fields (columns) from the Fields Pane into the table.
– Example: CustomerName, OrderDate, PizzaType, Quantity, TotalSales.
5. The table now shows raw data in tabular format.

6. Use formatting options:

– Sort by column (ascending/descending).
– Adjust column width.
– Apply conditional formatting (color scale, icons, data bars).
– Add totals/subtotals.

🔹 Example (Pizza Sales Dataset)

• Added a Table Visual to show:
– Columns: Order ID, Customer Name, Pizza Type, Quantity, Total Sales.
• Applied Sorting on Total Sales (highest to lowest).

• Applied Conditional Formatting on Total Sales:

– Green color for high sales, Red for low sales.
• Final result: A detailed table that allows managers to drill down to row-level
transactions.

🔹 Extra Features in Table Visual

• Search Box → Helps search for a specific record.
• Drill-through → Right-click a row → See detailed report for that record.
• Export Option → Tables can be exported to Excel/CSV if allowed.
• Subtotals/Grand Totals → Can be turned ON/OFF in formatting pane.

🔹 Why Use Tables?

• Shows transaction-level details that charts cannot show.
• Great for auditing data (e.g., checking if sales numbers match source).
• Useful when presenting lists of records (orders, customers, invoices).

⚠️Limitation: Tables are not visually appealing → Use them only when detail view is needed, not
for storytelling dashboards.
✅ That’s Topic 13 completed: Creating a Table Using Power BI.

Do you want me to continue with Topic 14: Pivoting and Unpivoting of Data in Power BI?

Perfect 👍 Let’s go to Topic 14: Pivoting and Unpivoting of Data in Power BI.

1️⃣4️⃣Pivoting and Unpivoting of Data in Power BI

🔹 Definition / Concept
When we work with raw datasets, sometimes data is not structured properly for analysis. Power
BI provides Pivot and Unpivot features in Power Query Editor to restructure data.

• Pivot → Converts rows into columns (like Excel PivotTable).

• Unpivot → Converts columns into rows (very common in cleaning datasets).

👉 Pivoting/Unpivoting is crucial because Power BI prefers long-format (tidy) data for visuals.

🔹 Steps for Pivot in Power Query

1. Load data → Go to Power Query Editor.
2. Select the column you want to keep as row labels (e.g., Pizza Type).
3. Select the column you want to Pivot (e.g., Month).
4. Go to Transform Tab → Pivot Column.
5. Select the value column (e.g., Sales Amount).
6. Result → Each unique value in Month becomes a separate column with Sales values.

Example (Pizza Sales Dataset):

Pizza Type Jan Sales Feb Sales Mar Sales

Margherita 1200 1500 1600
Veggie 800 950 1100

🔹 Steps for Unpivot in Power Query

1. Load data → Go to Power Query Editor.
2. Select the columns you want to keep fixed (e.g., Pizza Type).
3. Select the columns you want to convert into rows (e.g., Jan, Feb, Mar Sales).
4. Go to Transform Tab → Unpivot Columns.
 5. Result → Columns become rows with two new columns: Attribute (Month) and Value
(Sales).

Example (Pizza Sales Dataset):

Before (Wide Format):

Pizza Type Jan Feb Mar

Margherita 1200 1500 1600
Veggie 800 950 1100

After (Long Format):

Pizza Type Month Sales

Margherita Jan 1200
Margherita Feb 1500
Margherita Mar 1600
Veggie Jan 800
Veggie Feb 950
Veggie Mar 1100

🔹 Example from Video (Pizza Sales Dataset)

• The dataset had Sales by Month as separate columns.
• Used Unpivot to convert them into one column → Month, and one column → Sales.
• This made it easier to build a line chart for Sales Trend over time.

🔹 Why Pivot/Unpivot are Important?

• Many source files (Excel/CSV) are not in analysis-friendly format.
• Unpivot creates a long-format table → which Power BI visuals (line, bar, area charts)
work best with.
• Pivot is useful when you want a summary table with categories as columns.

⚠️Best practice: Keep data in Unpivoted (long) format for dashboarding. Pivot only when you
need summary tables.

✅ That’s Topic 14 completed: Pivoting and Unpivoting of Data in Power BI.

Shall I continue with Topic 15: Data Model and Importance of Data Modeling?

Great 👍 Now let’s move on to Topic 15: Data Model and Importance of Data Modeling in Power
BI.
1️⃣5️⃣Data Model and Importance of Data
Modeling in Power BI
🔹 Definition / Concept
A Data Model in Power BI is the logical structure that defines how tables are connected
through relationships.

👉 Think of it as the blueprint of your database inside Power BI.

• Tables (Orders, Customers, Products, Sales) are nodes.

• Relationships (CustomerID, ProductID, OrderID) are links between them.

This model allows Power BI to understand context and calculate values correctly.

🔹 Key Components of a Data Model

1. Tables → Contain rows & columns (like Orders, Customers).

2. Relationships → Connections between tables (like CustomerID links Orders to

Customers).

3. Keys →
– Primary Key (PK): Unique identifier in one table (CustomerID in Customers table).
– Foreign Key (FK): References PK in another table (CustomerID in Orders table).
4. Cardinality → Defines how tables relate (One-to-Many, Many-to-One, Many-to-
Many).

5. Data Types & Formats → Ensures proper matching across tables.

🔹 Steps to Build a Data Model in Power BI

1. Import all required datasets into Power BI.

2. Go to Model View (left-side icon).

3. Power BI automatically detects relationships → Adjust if needed.

4. Drag & drop fields to create manual relationships.

– Example: Connect Orders[CustomerID] → Customers[CustomerID].
5. Define Cardinality (One-to-Many, Many-to-One).
 6. Use Manage Relationships tool for fine-tuning.

🔹 Example (Pizza Sales Dataset)

• Orders Table → OrderID, CustomerID, PizzaID, Date, Quantity.
• Customers Table → CustomerID, Name, City.
• Products Table → PizzaID, PizzaName, Category, Price.
• Sales Table → OrderID, TotalAmount.

Relationships:

• Orders[CustomerID] → Customers[CustomerID] (One Customer → Many Orders).

• Orders[PizzaID] → Products[PizzaID] (One Product → Many Orders).
• Orders[OrderID] → Sales[OrderID] (One Order → One Sale).

This model allowed:

• Sales by Customer
• Sales by Pizza Type
• Sales by City

🔹 Importance of Data Modeling

✅ Accuracy in Reporting

• Without correct relationships, numbers will be duplicated or missed.

✅ Scalability

• A good model supports future growth (e.g., adding new tables like Employees,
Suppliers).

✅ Performance

• Optimized models refresh faster and consume less memory.

✅ Business Insights

• Enables powerful questions like:

– “Which city has the highest sales per customer?”
– “Which pizza contributes most to total revenue?”

🔹 Best Practices in Data Modeling

1. Use Star Schema → Fact Table (transactions) + Dimension Tables (customers, products,
dates).
2. Avoid using Many-to-Many relationships unless necessary.
 3. Keep column names clear & consistent.
4. Create a Date Table for proper time intelligence.
5. Hide unnecessary columns to simplify model.

✅ That’s Topic 15 completed: Data Model and Importance of Data Modeling in Power BI.

Do you want me to continue with Topic 16: Managing Data Relationships in Power BI?

Great 👍 Let’s move ahead to Topic 16: Managing Data Relationships in Power BI.

1️⃣6️⃣Managing Data Relationships in Power BI

🔹 Definition / Concept
A relationship in Power BI defines how two tables are connected using a common column (key).

• Example: Orders[CustomerID] connects to Customers[CustomerID].

• Once defined, Power BI can combine data from multiple tables into one report.

👉 Relationships are the foundation of the data model we just discussed.

🔹 Types of Relationships
1. One-to-Many (1:*) → Most common
– Example: One Customer → Many Orders.
2. Many-to-One (*:1) → Reverse of above
– Example: Many Orders → One Customer.
3. One-to-One (1:1)
– Example: One Employee → One Company Car.
4. Many-to-Many (:)
– Example: Many Students → Many Courses.
– ⚠️Should be avoided unless necessary (can cause calculation issues).

🔹 Steps to Manage Relationships in Power BI

1. Go to Model View (left-side icon).

2. Power BI automatically detects some relationships.

 – These are shown as lines between tables.
3. To create or edit manually:
– Drag a column from one table → Drop it on the matching column in another table.
– Example: Drag Orders[CustomerID] → Customers[CustomerID].
4. Double-click the relationship line → Opens Edit Relationship dialog.
– Choose Cardinality: One-to-Many, etc.
– Choose Cross-filter direction (Single or Both).
– Choose whether relationship is Active or Inactive.
5. Save changes.

🔹 Example (Pizza Sales Dataset)

• Orders Table → OrderID, CustomerID, PizzaID.
• Customers Table → CustomerID, Name, City.
• Products Table → PizzaID, PizzaName, Category, Price.

Relationships Created:

• Orders[CustomerID] → Customers[CustomerID] (One-to-Many).

• Orders[PizzaID] → Products[PizzaID] (One-to-Many).

Now, a single visual can answer: 👉 “Which customer ordered the most Veggie Pizzas?” 👉 “Which
city contributes the highest sales?”

🔹 Cross-Filter Direction
• Single → Filters flow in one direction only.
– Example: Customers filter Orders, but Orders don’t filter Customers.
• Both → Filters work both ways.
– Example: If you filter Customers by City = “Delhi,” it filters Orders too.

⚠️Best Practice: Use Single unless you really need Both (to avoid performance issues).

🔹 Why Managing Relationships is Important?

✅ Ensures accurate calculations (avoids duplication). ✅ Enables combining multiple tables into a
single dashboard. ✅ Supports advanced analysis (Customer → Orders → Sales). ✅ Provides
flexibility → You don’t need to keep everything in one big table.
🔹 Best Practices
1. Always use unique primary keys in dimension tables.
2. Avoid circular relationships (loops).
3. Prefer Star Schema design → One central fact table + multiple dimension tables.
4. Use “Manage Relationships” window to double-check connections.

✅ That’s Topic 16 completed: Managing Data Relationships in Power BI.

Shall I continue with Topic 17: Cardinality and Cross-Filter Direction in Power BI?

Awesome 👍 Let’s dive into Topic 17: Cardinality and Cross-Filter Direction in Power BI.

1️⃣7️⃣Cardinality and Cross-Filter Direction in

Power BI
🔹 Definition / Concept
When you connect tables in Power BI, the relationship settings define how data interacts:

1. Cardinality → Defines how many rows in one table relate to rows in another table.
2. Cross-Filter Direction → Defines how filters flow between related tables.

These two settings control how calculations behave in visuals.

🔹 Cardinality in Power BI
Cardinality = “How many-to-how many” relationship exists between tables.

Types of Cardinality:
1. One-to-Many (1:*) ✅ Most common
– Example: One Customer → Many Orders.
– Customers[CustomerID] = 1, Orders[CustomerID] = Many.
2. Many-to-One (*:1)
– Same as above but reversed.
– Orders → Customers.
3. One-to-One (1:1)
 – Example: One Employee → One ID Card.
– Rare, used when tables have a strict one-to-one match.
4. Many-to-Many (:) ⚠️Risky
– Example: Many Students → Many Courses.
– Used when both tables don’t have a unique key.
– Can create ambiguous calculations.

👉 Best practice: Use One-to-Many relationships whenever possible.

🔹 Cross-Filter Direction
Cross-filter direction defines how filters flow between tables.

1. Single Direction (→)

– Filters flow only from one side to the other.
– Example: Customers filter Orders, but Orders don’t filter Customers.
– Safer, better for performance.
2. Both Directions (↔)
– Filters flow in both directions.
– Example: Filtering Orders also filters Customers.
– Useful for complex models with multiple fact tables.
– ⚠️Can cause performance issues or circular references.

🔹 Example (Pizza Sales Dataset)

Tables:

• Customers → CustomerID, Name, City.

• Orders → OrderID, CustomerID, PizzaID, Quantity.
• Products → PizzaID, PizzaName, Price.

Relationships:

• Customers[CustomerID] (1) → Orders[CustomerID] (*) → One-to-Many.

• Products[PizzaID] (1) → Orders[PizzaID] (*) → One-to-Many.

Cross-Filter Example:

• If set to Single → Choosing “Delhi” in Customers filters Orders.

• If set to Both → Filtering Orders by “Veggie Pizza” also filters Customers (only Delhi
customers who ordered Veggie Pizza).
🔹 Why Cardinality & Cross-Filter Matter?
✅ Ensures correct aggregations (avoids duplication). ✅ Defines how filters behave in visuals. ✅
Helps handle complex models with multiple tables. ✅ Prevents wrong results in DAX measures.

🔹 Best Practices
1. Always ensure dimension tables have unique keys.
2. Use One-to-Many relationships wherever possible.
3. Keep Single filter direction by default; switch to Both only if needed.
4. Test relationships → Drag fields into visuals to confirm results.
5. Avoid Many-to-Many unless you’re confident with advanced modeling.

✅ That’s Topic 17 completed: Cardinality and Cross-Filter Direction in Power BI.

Do you want me to continue with Topic 18: Introduction to DAX and Its Importance in Power
BI?

Perfect 👍 Now let’s move to Topic 18: Introduction to DAX and Its Importance in Power BI.

1️⃣8️⃣Introduction to DAX and Its Importance in

Power BI
🔹 What is DAX?
DAX (Data Analysis Expressions) is a formula language used in Power BI, Excel Power Pivot, and
Analysis Services.

👉 It is similar to Excel formulas, but designed for data modeling and analytics.

• Works with tables and relationships.

• Helps create custom calculations beyond the raw data.

🔹 Key Uses of DAX in Power BI

1. Calculated Columns → Add new columns based on formulas.
– Example: Profit = Sales – Cost.
2. Measures → Perform calculations on aggregated data.
 – Example: Total Sales = SUM(Sales[Amount]).
3. Calculated Tables → Create new tables using formulas.
– Example: Filtered tables with only High-Sales Customers.

🔹 Why DAX is Important?

✅ Advanced Analytics

• Allows you to go beyond built-in aggregations (Sum, Avg).

• Example: Running totals, moving averages, YoY growth.

✅ Dynamic Reporting

• Measures change dynamically when filters/slicers are applied.

• Example: “Top 5 Pizzas by Sales in Delhi for 2024.”

✅ Time Intelligence

• Handle complex date calculations easily.

• Example: Sales MTD (Month to Date), Sales YTD (Year to Date), Previous Month Sales.

✅ Data Modeling Flexibility

• Lets you build KPIs, ratios, rankings, and comparisons.

🔹 Example (Pizza Sales Dataset)

Suppose we have:

• Orders Table: OrderID, CustomerID, PizzaID, Quantity.

• Products Table: PizzaID, Price.
1. Calculated Column Example
Total_Sales = Orders[Quantity] * RELATED(Products[Price])

👉 Adds a new column per row showing total sales per order.

1. Measure Example
Total Revenue = SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price]))

👉 A measure that calculates total revenue across all rows.

1. Time Intelligence Example

Sales_MTD = TOTALMTD([Total Revenue], Calendar[Date])

👉 Calculates Month-to-Date sales.

🔹 Difference Between Excel Formulas & DAX
Feature Excel Formulas DAX in Power BI
Works Single cell Entire column / table
On
Context No concept of filter context Aware of filter & row context
Use Case Personal analysis Business-level analytics

🔹 Why Every Power BI Developer Must Learn DAX?

• Without DAX, Power BI is just a visualization tool.
• With DAX, Power BI becomes a powerful analytical engine.
• Enables KPIs, dashboards, and business decisions.

✅ That’s Topic 18 completed: Introduction to DAX and Its Importance in Power BI.

Do you want me to continue with Topic 19: DAX in Power BI (basics of syntax and usage)?

Great 👍 Let’s continue with Topic 19: DAX in Power BI.

1️⃣9️⃣DAX in Power BI
🔹 What is DAX in Power BI?
DAX (Data Analysis Expressions) is the calculation language of Power BI.

• It allows you to define custom calculations for tables, columns, and measures.
• Works directly with your data model (tables + relationships).
• Uses functions, operators, and constants.

👉 Without DAX, Power BI would only show basic aggregations (Sum, Count, Average). With
DAX, you can create dynamic, business-ready metrics.

🔹 Structure of DAX Formulas

A DAX formula generally follows this structure:

Result = FunctionName(Table/Column, Expression)

Example:

Total Sales = SUM(Sales[Amount])

• Total Sales → Name of measure.

• SUM → DAX function.
• Sales[Amount] → Column reference.

🔹 Key Features of DAX in Power BI

1. Calculated Columns
– Adds a new column at the row level.
– Example:
Profit = Sales[Revenue] - Sales[Cost]

2. Measures
– Calculates values on aggregated data.
– Example:
Total Revenue = SUM(Sales[Revenue])

3. Calculated Tables
– Creates new tables from existing data.
– Example:
HighSales = FILTER(Sales, Sales[Revenue] > 1000)

4. Row Context vs Filter Context

– Row Context → Evaluates expression for each row.
– Filter Context → Evaluates expression after filters (slicers, visuals) are applied.

🔹 Example (Pizza Sales Dataset)

• Column Example:

Total_Sales = Orders[Quantity] * RELATED(Products[Price])

(Adds a per-order sales column).

• Measure Example:

Total Revenue = SUMX(Orders, Orders[Quantity] *

RELATED(Products[Price]))
 (Aggregates sales dynamically, considering filters like City or Month).

• Calculated Table Example:

TopCustomers = TOPN(5, SUMMARIZE(Orders, Customers[Name],

"Revenue", SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price]))), [Revenue], DESC)

(Creates a table of top 5 customers by revenue).

🔹 Why DAX is Critical in Power BI?

✅ Enables business metrics (Profit Margin, Year-over-Year Growth, Customer Retention). ✅
Makes dashboards interactive and dynamic. ✅ Provides flexibility similar to SQL + Excel
combined. ✅ Essential for KPIs, forecasting, and advanced analytics.

✅ That’s Topic 19 completed: DAX in Power BI (basic usage).

Do you want me to continue with Topic 20: Steps to Create Calculated Columns in DAX -
Power BI?

Perfect 👍 Let’s move on to Topic 20: Steps to Create Calculated Columns in DAX - Power BI.

22️⃣00️⃣
Steps to Create Calculated Columns in DAX -
Power BI
🔹 What is a Calculated Column?
A Calculated Column is a new column you create in a table using DAX formulas.

• It is calculated row by row.

• Stored in the data model (takes up memory).
• Useful for row-level calculations (like profit per order, category labels, flags).

👉 Think of it like adding a new formula column in Excel.

🔹 Steps to Create a Calculated Column in Power BI
1. Open Power BI Desktop.
2. Go to Data View (left-side icon that looks like a table).
3. Select the table where you want to add the column.
4. On the top ribbon, click Modeling → New Column.
5. A formula bar appears → Write your DAX formula.
6. Press Enter → A new column is created.

🔹 Example Formulas
1. Profit per Order
Profit = Sales[Revenue] - Sales[Cost]

1. Total Sales Amount

Total_Sales = Orders[Quantity] * RELATED(Products[Price])

1. Category Label (Flagging High/Low Sales)

Sales_Category = IF(Sales[Revenue] > 1000, "High", "Low")

1. Extract Year from Date

Order_Year = YEAR(Sales[OrderDate])

🔹 Example (Pizza Sales Dataset)

• Orders Table had Quantity and PizzaID.
• Products Table had PizzaID and Price.
• Using RELATED function:
Total_Sales = Orders[Quantity] * RELATED(Products[Price])

This created a new column per row showing how much revenue each order generated.

🔹 When to Use Calculated Columns?

✅ When you need row-level calculations.

• Example: Order total, profit per item. ✅ When you want to categorize data.
• Example: High/Low Sales, Age Group. ✅ When the column will be used in relationships
or grouping.

⚠️Not good for aggregations → Use Measures instead (because calculated columns take
memory and don’t change dynamically with filters).
🔹 Best Practices
1. Use Calculated Columns for row-based logic (not totals).
2. Prefer Measures for aggregations to save memory.
3. Use RELATED() to fetch values from related tables.
4. Avoid unnecessary columns → Optimize performance.

✅ That’s Topic 20 completed: Steps to Create Calculated Columns in DAX - Power BI.

Do you want me to continue with Topic 21: Creation of Measures in Power BI and Its Types?

Great 👍 Let’s move to Topic 21: Creation of Measures in Power BI and Its Types.

2️⃣1️⃣Creation of Measures in Power BI and Its

Types
🔹 What is a Measure?
A Measure in Power BI is a DAX calculation used to perform aggregations on your data model.

👉 Unlike Calculated Columns:

• Measures are not stored in the table → They are calculated on the fly.
• Measures respond to filters, slicers, and visuals dynamically.
• They are memory-efficient compared to Calculated Columns.

🔹 Steps to Create a Measure in Power BI

1. Open Power BI Desktop.
2. Select the table where you want the measure to belong.
3. Go to the Modeling tab → New Measure.
4. Enter the DAX formula in the formula bar.
5. Press Enter → The measure is created and appears in the Fields pane (with a calculator
icon).
6. Drag the measure into visuals to use it.

🔹 Example Formulas (Pizza Sales Dataset)

1. Total Revenue (SUM)
Total Revenue = SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price]))

👉 Dynamically calculates sales revenue across all orders.

1. Total Quantity Sold

Total Quantity = SUM(Orders[Quantity])

1. Average Revenue per Customer

Avg Revenue per Customer = AVERAGEX(Customers, CALCULATE(SUMX(Orders,
Orders[Quantity] * RELATED(Products[Price]))))

1. Customer Count
Customer Count = DISTINCTCOUNT(Orders[CustomerID])

1. Profit Margin %
Profit Margin % = DIVIDE([Total Revenue] - SUM(Orders[Cost]), [Total
Revenue])

🔹 Types of Measures
1. Simple Aggregations
– SUM, AVERAGE, COUNT, MIN, MAX.
– Example: Total Sales = SUM(Sales[Amount]).
2. Iterative Functions (X Functions)
– SUMX, AVERAGEX, COUNTX → Work row by row.
– Example: SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price])).
3. Time Intelligence Measures
– Functions like TOTALYTD, SAMEPERIODLASTYEAR, DATEADD.
– Example:
Sales YTD = TOTALYTD([Total Revenue], Calendar[Date])

4. Statistical/Logical Measures
– IF, SWITCH, RANKX.
– Example:
Top Pizza = RANKX(ALL(Products[PizzaName]), [Total Revenue], ,
DESC)
🔹 Difference Between Calculated Column & Measure
Feature Calculated Column Measure
Storage Stored in data model (takes space) Not stored (calculated on demand)
Evaluation Row by row Aggregate / based on filters
Performan Slower (more memory) Faster, optimized
ce
Use Case Row-level logic (per transaction) Totals, KPIs, Aggregations

🔹 Why Use Measures?

✅ Memory-efficient → No extra storage. ✅ Dynamic → Updates instantly with filters & slicers. ✅
Essential for KPIs, Dashboards, and Advanced Analytics.

✅ That’s Topic 21 completed: Creation of Measures in Power BI and Its Types.

Do you want me to continue with Topic 22: Understanding DAX Syntax in Power BI?

Perfect 👍 Let’s move to Topic 22: Understanding DAX Syntax in Power BI.

2️⃣2️⃣Understanding DAX Syntax in Power BI

🔹 Structure of a DAX Formula
Every DAX expression follows a structured format:

<Measure/Column Name> = <Function or Expression>

• Measure/Column Name → User-defined name (e.g., Total Sales).

• = → Assignment operator.
• Function/Expression → DAX calculation (e.g., SUM(Sales[Amount])).

🔹 Key Elements of DAX Syntax

1. Functions
– DAX has 250+ built-in functions.
– Categories: Aggregation, Logical, Date & Time, Statistical, Text, Time Intelligence.
 – Example: SUM(Sales[Amount]).
2. Columns & Tables
– Always referenced in square brackets [ ].
– Example: Sales[Revenue] → Refers to Revenue column in Sales table.
3. Operators
– Mathematical: + - * / ^
– Comparison: =, >, <, >=, <=, <>
– Logical: && (AND), || (OR), NOT.
– Example: IF(Sales[Revenue] > 1000, "High", "Low").
4. Row Context
– When a formula is applied row by row.
– Example in a calculated column: Profit = Sales[Revenue] -
Sales[Cost].
5. Filter Context
– When filters/slicers affect calculations.
– Example: A measure Total Sales = SUM(Sales[Amount]) → behaves
differently if a slicer for “Delhi” is applied.
6. Nested Functions
– You can use multiple functions inside each other.
– Example:
Sales Growth % = DIVIDE(SUM(Sales[Amount]) -
CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR)),
CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR)))

(Calculates Year-over-Year Sales Growth %).

🔹 Example (Pizza Sales Dataset)

1. Simple Measure
Total Revenue = SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price]))

1. Conditional Logic
High Sales Flag = IF([Total Revenue] > 5000, "High", "Low")

1. Time Intelligence
Sales MTD = TOTALMTD([Total Revenue], Calendar[Date])
🔹 Best Practices for Writing DAX
1. Always name measures clearly (e.g., use Total Sales instead of Measure1).
2. Use indentation and line breaks for readability.
3. Keep formulas optimized → Avoid unnecessary calculated columns.
4. Use variables (VAR) in complex formulas for clarity and performance.

Example with VAR:

Profit Margin % =
VAR Revenue = [Total Revenue]
VAR Cost = SUM(Orders[Cost])
RETURN DIVIDE(Revenue - Cost, Revenue)

✅ That’s Topic 22 completed: Understanding DAX Syntax in Power BI.

Do you want me to continue with Topic 23: DAX Functions in Power BI?

Great 👍 Let’s move on to Topic 23: DAX Functions in Power BI.

2️⃣3️⃣DAX Functions in Power BI

🔹 What are DAX Functions?
DAX functions are predefined formulas in Power BI used to perform calculations on data.

• They work with tables, columns, and relationships.

• Similar to Excel functions, but much more powerful because they respect row context
and filter context.

👉 Power BI has 250+ DAX functions categorized into groups.

🔹 Categories of DAX Functions

1. Aggregate Functions
Perform summary operations.

• SUM(Column) → Adds values.

• AVERAGE(Column) → Finds average.
 • COUNT(Column) → Counts rows with values.
• DISTINCTCOUNT(Column) → Counts unique values.

Example:

Total Sales = SUM(Sales[Amount])

Customer Count = DISTINCTCOUNT(Sales[CustomerID])

2. Filter Functions
Return filtered data.

• FILTER(Table, Expression) → Returns a subset of a table.

• ALL(Table) → Removes all filters.
• CALCULATE(Expression, Filter1, Filter2, …) → Evaluates an expression
under given filters.

Example:

Delhi Sales = CALCULATE(SUM(Sales[Amount]), Customers[City] = "Delhi")

3. Logical Functions
Used for conditions.

• IF(Condition, TrueResult, FalseResult)

• AND(Condition1, Condition2) or &&
• OR(Condition1, Condition2) or ||
• SWITCH(Expression, Value1, Result1, Value2, Result2, …)

Example:

Sales Category = IF(Sales[Amount] > 1000, "High", "Low")

4. Text Functions
Manipulate text data.

• CONCATENATE(Text1, Text2)
• LEFT(Text, NumberOfChars)
• RIGHT(Text, NumberOfChars)
• UPPER(Text) / LOWER(Text)
Example:

Customer Full Name = CONCATENATE(Customers[FirstName], " " &

Customers[LastName])

5. Date & Time Functions

Handle time-based data.

• YEAR(DateColumn)
• MONTH(DateColumn)
• TODAY()
• DATEDIFF(StartDate, EndDate, Interval)
• TOTALYTD(Expression, DateColumn)

Example:

Order Year = YEAR(Sales[OrderDate])

Sales YTD = TOTALYTD([Total Sales], Calendar[Date])

6. Time Intelligence Functions

Used for comparisons across time.

• SAMEPERIODLASTYEAR(DateColumn)
• DATEADD(DateColumn, -1, YEAR)
• PARALLELPERIOD(DateColumn, -1, MONTH)

Example:

Sales Last Year = CALCULATE([Total Sales],

SAMEPERIODLASTYEAR(Calendar[Date]))

7. Statistical Functions
• MEDIAN(Column)
• RANKX(Table, Expression)

Example:

Top Pizza Rank = RANKX(ALL(Products[PizzaName]), [Total Sales], ,

DESC)
8. Iterators (X Functions)
Perform row-by-row evaluation.

• SUMX(Table, Expression)
• AVERAGEX(Table, Expression)
• MAXX(Table, Expression)

Example:

Revenue = SUMX(Orders, Orders[Quantity] * RELATED(Products[Price]))

🔹 Example (Pizza Sales Dataset)

• Total Revenue →
Total Revenue = SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price]))

• High Sales Customers →

High Sales Customers = CALCULATE(DISTINCTCOUNT(Orders[CustomerID]),
[Total Revenue] > 1000)

• Sales Growth % →
Sales Growth % =
DIVIDE([Total Revenue] - [Sales Last Year], [Sales Last Year])

🔹 Why Use DAX Functions?

✅ Enable complex business logic. ✅ Work across multiple tables & relationships. ✅ Make reports
dynamic with slicers and filters. ✅ Provide KPIs, trends, and insights.

✅ That’s Topic 23 completed: DAX Functions in Power BI.

Do you want me to continue with Topic 24: Date and Time Functions in DAX - Power BI?

Got it 👍 I’ll continue one by one without asking again. Let’s move forward.
2️⃣4️⃣Date and Time Functions in DAX - Power BI
🔹 What are Date & Time Functions?
DAX provides Date and Time functions to work with calendar-based data.

• Useful for time-based reporting like Monthly Sales, Yearly Growth, YTD, MTD.
• Many of these are Time Intelligence functions that automatically respect filters and
calendars.

🔹 Common Date & Time Functions

1. TODAY() → Returns current date.

TodayDate = TODAY()

2. NOW() → Returns current date and time.

CurrentDateTime = NOW()

3. YEAR(DateColumn) → Extracts year.

OrderYear = YEAR(Sales[OrderDate])

4. MONTH(DateColumn) → Extracts month (1–12).

OrderMonth = MONTH(Sales[OrderDate])

5. DAY(DateColumn) → Extracts day of the month.

OrderDay = DAY(Sales[OrderDate])

6. WEEKDAY(DateColumn) → Returns day of week (1 = Sunday, 2 = Monday, etc.).

OrderWeekDay = WEEKDAY(Sales[OrderDate])

7. EOMONTH(StartDate, Months) → Returns end of month date.

EndOfMonth = EOMONTH(Sales[OrderDate], 0)

8. DATEDIFF(StartDate, EndDate, Interval) → Returns difference between two

dates.

DeliveryDays = DATEDIFF(Sales[OrderDate], Sales[DeliveryDate],

DAY)
 9. DATEADD(DateColumn, Number, Interval) → Shifts dates forward/backward.

SalesLastMonth = CALCULATE([Total Revenue],

DATEADD(Calendar[Date], -1, MONTH))

10. TOTALYTD(Expression, DateColumn) → Year-to-date calculation.

SalesYTD = TOTALYTD([Total Revenue], Calendar[Date])

1. TOTALMTD(Expression, DateColumn) → Month-to-date.

SalesMTD = TOTALMTD([Total Revenue], Calendar[Date])

1. TOTALQTD(Expression, DateColumn) → Quarter-to-date.

SalesQTD = TOTALQTD([Total Revenue], Calendar[Date])

1. SAMEPERIODLASTYEAR(DateColumn) → Compares with last year’s same period.

SalesLastYear = CALCULATE([Total Revenue],
SAMEPERIODLASTYEAR(Calendar[Date]))

🔹 Example (Pizza Sales Dataset)

• Monthly Sales:

SalesMTD = TOTALMTD([Total Revenue], Calendar[Date])

• Yearly Sales Comparison:

SalesGrowth% =
DIVIDE([Total Revenue] - [SalesLastYear], [SalesLastYear])

• Delivery Time (days):

DeliveryDays = DATEDIFF(Orders[OrderDate], Orders[DeliveryDate],

DAY)

🔹 Why Important?
✅ Time-based KPIs (Monthly, Quarterly, Yearly). ✅ Business trend analysis (YoY, MoM). ✅
Forecasting & financial reporting. ✅ Helps build time intelligence dashboards.
2️⃣5️⃣Text Functions Using DAX in Power BI
🔹 What are Text Functions?
Text functions allow you to manipulate and format string/text data in Power BI. Useful for
creating labels, concatenations, formatting names, or extracting parts of text.

🔹 Common Text Functions

1. CONCATENATE(Text1, Text2) → Joins two strings.

FullName = CONCATENATE(Customers[FirstName], " " &

Customers[LastName])

2. CONCATENATEX(Table, Expression, Delimiter) → Concatenates multiple values

with a delimiter.

PizzaList = CONCATENATEX(Products, Products[PizzaName], ", ")

3. LEFT(Text, NumberOfChars) → Extracts characters from the left.

First3 = LEFT(Customers[Name], 3)

4. RIGHT(Text, NumberOfChars) → Extracts characters from the right.

Last4 = RIGHT(Orders[OrderID], 4)

5. MID(Text, Start, NumChars) → Extracts text from the middle.

SubPart = MID(Customers[Phone], 3, 4)

6. LEN(Text) → Returns string length.

NameLength = LEN(Customers[Name])

7. UPPER(Text) / LOWER(Text) → Converts text case.

UpperName = UPPER(Customers[Name])
LowerName = LOWER(Customers[Name])

8. REPLACE(Text, Start, NumChars, NewText) → Replaces part of text.

MaskPhone = REPLACE(Customers[Phone], 4, 3, "***")

 9. SEARCH(FindText, WithinText, StartPosition) → Finds position of substring.

FindPizza = SEARCH("Veg", Products[PizzaName], 1)

10. TRIM(Text) → Removes extra spaces.

CleanName = TRIM(Customers[Name])

🔹 Example (Pizza Sales Dataset)

• Customer full name from First & Last Name:

FullName = CONCATENATE(Customers[FirstName], " " &

Customers[LastName])

• Masked phone numbers:

MaskPhone = REPLACE(Customers[Phone], 4, 3, "***")

• Extract Pizza type keyword:

IsVeg = IF(SEARCH("Veg", Products[PizzaName], 1) > 0, "Veg",

"Non-Veg")

🔹 Why Important?
✅ Helps clean and prepare text data. ✅ Useful for creating labels & tooltips in visuals. ✅ Makes
dashboards user-friendly with formatted text.

2️⃣6️⃣Logical Functions Using DAX in Power BI

🔹 What are Logical Functions?
Logical functions allow you to apply conditional logic in calculations. They work like IF-ELSE
statements in programming.

🔹 Common Logical Functions

1. IF(Condition, TrueResult, FalseResult)
 HighSales = IF(Sales[Amount] > 1000, "High", "Low")

2. AND / &&

PremiumCustomer = IF(Customers[City] = "Delhi" && [Total Revenue]

> 5000, "Yes", "No")

3. OR / ||

DiscountCustomer = IF(Customers[City] = "Delhi" ||

Customers[City] = "Mumbai", "Yes", "No")

4. NOT()

NonVeg = IF(NOT(Products[PizzaName] = "Veggie Pizza"), "Non-Veg",

"Veg")

5. SWITCH(Expression, Value1, Result1, Value2, Result2, …)

RatingCategory = SWITCH(TRUE(),
[Avg Rating] >= 4.5, "Excellent",
[Avg Rating] >= 3.5, "Good",
[Avg Rating] >= 2.5, "Average",
"Poor")

🔹 Example (Pizza Sales Dataset)

• Classify orders as High/Low Sales:

SalesCategory = IF([Total Revenue] > 2000, "High", "Low")

• Categorize Pizza Ratings:

RatingCategory = SWITCH(TRUE(),
[Avg Rating] >= 4.5, "Excellent",
[Avg Rating] >= 3.5, "Good",
[Avg Rating] >= 2.5, "Average",
"Poor")

🔹 Why Important?
✅ Helps in categorization and segmentation. ✅ Enables creation of flags, KPIs, and
classifications. ✅ Essential for business rules inside dashboards.
Next, I’ll cover Topic 27: Using ChatGPT to Create Measures.

2️⃣7️⃣Using ChatGPT to Create Measures in Power

BI
🔹 Why Use ChatGPT for DAX?
Writing DAX measures can be challenging, especially for beginners. ChatGPT (or other AI tools)
can help:

• Generate DAX formulas based on natural language queries.

• Debug and optimize existing DAX code.
• Suggest best practices and alternatives.

👉 Example: Instead of struggling with syntax, you can ask ChatGPT:

“Write a DAX measure to calculate Year-over-Year Sales Growth % in Power BI.”

And ChatGPT will provide a ready-to-use formula.

🔹 Steps to Use ChatGPT for DAX in Power BI

1. Identify the Requirement
– Example: “I want to calculate the top 5 pizzas by revenue.”
2. Ask ChatGPT in Natural Language
– Example Query:
“Give me a DAX measure to calculate top 5 products by total sales revenue.”
3. Copy the Generated Formula into Power BI → New Measure.

4. Test in a Visual (Table, Card, KPI).

5. Validate Results → Check if numbers match expectations.

🔹 Example Prompts & Results

1. Prompt: “Write a DAX measure to calculate Total Sales Revenue.” Result:
 Total Revenue = SUMX(Orders, Orders[Quantity] *
RELATED(Products[Price]))

1. Prompt: “Write a DAX measure to calculate YoY Sales Growth %.” Result:

Sales Growth % =
DIVIDE(
[Total Revenue] - CALCULATE([Total Revenue],
SAMEPERIODLASTYEAR(Calendar[Date])),
CALCULATE([Total Revenue],
SAMEPERIODLASTYEAR(Calendar[Date]))
)

1. Prompt: “Write a DAX measure to rank customers by total revenue.” Result:

Customer Rank = RANKX(ALL(Customers[CustomerName]), [Total

Revenue], , DESC)

🔹 Benefits of Using ChatGPT for Measures

✅ Saves time in writing complex formulas. ✅ Helps beginners learn DAX faster. ✅ Provides
optimized versions of calculations. ✅ Good for debugging syntax errors.

🔹 Caution
⚠️ChatGPT might give formulas that don’t exactly fit your dataset (different table/column
names). 👉 Always adjust table/column references before using. 👉 Validate numbers carefully
before deploying in reports.

2️⃣8️⃣DAX Operators
🔹 What are DAX Operators?
Operators are symbols in DAX used to perform calculations, comparisons, and logical tests.
They are the building blocks of formulas.
🔹 Types of DAX Operators
1. Arithmetic Operators
– + → Addition
– - → Subtraction
– * → Multiplication
– / → Division
– ^ → Exponentiation
Example:

Profit = Sales[Revenue] - Sales[Cost]

DiscountPrice = Sales[Price] * 0.9

1. Comparison Operators
– = → Equal
– <> → Not Equal
– > → Greater than
– < → Less than
– >= → Greater than or equal
– <= → Less than or equal
Example:

HighSales = IF(Sales[Amount] >= 1000, "High", "Low")

1. Text Concatenation Operator

– & → Joins text strings.
Example:

FullName = Customers[FirstName] & " " & Customers[LastName]

1. Logical Operators
– && → AND
– || → OR
– NOT() → Negation
Example:
 PremiumCustomer = IF(Customers[City] = "Delhi" && [Total Revenue]
> 5000, "Yes", "No")

1. IN Operator (for multiple values check)

MetroCustomers = IF(Customers[City] IN {"Delhi", "Mumbai",

"Bangalore"}, "Metro", "Non-Metro")

🔹 Example (Pizza Sales Dataset)

• Profit Calculation:

Profit = Sales[Revenue] - Sales[Cost]

• High Revenue Flag:

SalesFlag = IF([Total Revenue] > 2000, "High", "Low")

• Customer Label:

CustomerLabel = Customers[FirstName] & " " & Customers[LastName]

🔹 Why Important?
✅ Operators are core syntax of DAX. ✅ Combine with functions for business logic. ✅ Enable
comparisons, math, and string formatting.

Next, I’ll cover Topic 29: Introduction to Visuals in Power BI.

Perfect 👍 Let’s keep going without breaks. I’ll cover all remaining topics one by one with
definitions, steps, and examples.
2️⃣9️⃣Introduction to Visuals in Power BI
🔹 What are Visuals?
Visuals are graphical representations of data in Power BI that help you analyze and
communicate insights.

• Convert raw data into charts, tables, KPIs, and dashboards.

• Are interactive (linked with slicers, filters, and drill-through).

🔹 Common Visuals in Power BI

• Bar/Column Chart → Compare categories.
• Line Chart → Show trends over time.
• Pie/Donut Chart → Show proportions.
• Table/Matrix → Display detailed data.
• KPI Cards → Show key metrics (Total Sales, Revenue).
• Map & Filled Map → Geographic analysis.
• TreeMap → Hierarchical data visualization.
• Gauge → Performance against a target.

🔹 Steps to Add a Visual in Power BI

1. Load your dataset into Power BI.
2. Go to Report View.
3. In the Visualizations pane, select the chart type (e.g., Bar Chart).
4. Drag required fields (columns) into Axis, Values, Legend.
5. Format using the Format pane (colors, labels, titles).

🔹 Example (Pizza Dataset)

• Add a Bar Chart → Axis = Pizza Type, Values = Total Revenue.
• Result → You can instantly see which pizza contributes the most sales.

3️⃣0️⃣Visualization Charts in Power BI

🔹 Common Charts and Their Usage
1. Clustered Bar/Column Chart → Compare categories. Example: Sales by Pizza Type.
2. Stacked Column Chart → Show category + sub-category. Example: Sales by Pizza Type
split by City.
3. Line Chart → Trend over time. Example: Monthly Sales Trend.
 4. Area Chart → Line chart with filled area.
5. Pie/Donut Chart → Show percentage contribution. Example: Market Share of Pizza
Flavors.
6. Combo Chart (Line + Bar) → Compare two measures. Example: Sales & Profit trend
together.

3️⃣1️⃣Filtering Options in Power BI

🔹 Types of Filters
1. Visual-level Filters → Affect only one chart.
2. Page-level Filters → Affect all visuals on one page.
3. Report-level Filters → Affect all pages in the report.
4. Drill-through Filters → Allow deep dive into details.

🔹 Steps to Apply a Filter

1. Select visual or page.
2. Open Filters pane.
3. Drag a field (e.g., City).
4. Choose filter condition (Delhi only, Revenue > 5000, etc.).

3️⃣2️⃣Exploring Matrix Visuals in Power BI

🔹 What is a Matrix?
Matrix = Excel Pivot Table equivalent.

• Rows + Columns + Values.

• Allows drill-down and subtotals.

🔹 Example (Pizza Dataset)

• Rows = Pizza Type, Columns = Month, Values = Total Revenue.
• Shows pizza revenue month by month in a grid.
3️⃣3️⃣Filtering Data with Slicers in Power BI
🔹 What is a Slicer?
A visual filter that allows users to select values (like dropdowns/buttons).

• Easy to filter across entire report.

🔹 Example
Add a Slicer → City → Select “Delhi” → Dashboard updates only for Delhi’s sales.

3️⃣4️⃣Number Cards and Text Cards

🔹 Cards in Power BI
• Number Card → Shows single value (e.g., Total Revenue).
• Text Card → Display text/KPI label.

🔹 Example
• Card → Total Revenue = 25,00,000.
• Text Card → “Top Pizza: Margherita”.

3️⃣5️⃣KPI Visuals
🔹 What is KPI?
Key Performance Indicator (KPI) → Tracks performance vs. target.

• Fields:
– Indicator → Current Value (e.g., Total Sales).
– Trend Axis → Time (e.g., Month).
– Target → Benchmark (e.g., Target Sales).

🔹 Example
• KPI → Revenue vs Target.
– Indicator = Total Revenue.
 – Target = 20,00,000.
– If current revenue = 18,00,000 → KPI shows performance gap.

3️⃣6️⃣Visualizing Data with Maps in Power BI

🔹 Types of Maps
1. Basic Map → Pins on locations.
2. Filled Map → Shaded regions by value.

🔹 Example
• Orders by City.
– Latitude & Longitude → Pin customers.
– Revenue → Bubble size.

3️⃣7️⃣TreeMap in Power BI
🔹 What is a TreeMap?
• Hierarchical visualization (like nested boxes).
• Each box = category, size = value.

🔹 Example
• Pizza Types as categories.
• Box size = Total Revenue.
• Instantly shows top contributors.

3️⃣8️⃣Tooltips in Power BI
🔹 What is a Tooltip?
Tooltip = Extra info when hovering over a chart element.

🔹 Example
• Hover on Pizza slice in Pie Chart → Shows “Total Orders, Revenue, Profit Margin”.
3️⃣9️⃣Modifying Colors in Charts and Visuals
🔹 Options Available
• Change Data Colors (each category).
• Conditional Formatting (e.g., sales > 5000 = Green, else Red).
• Themes (apply across report).

🔹 Example
• Pizza Sales Chart → Margherita = Blue, Farmhouse = Green, Veggie = Orange.

4️⃣0️⃣Bookmarks and Buttons in Power BI

🔹 Bookmarks
• Save current state of report (filters, visuals).
• Useful for storytelling.

🔹 Buttons
• Add navigation buttons (Next Page, Reset Filter, Show/Hide Visuals).

🔹 Example
• Create a Bookmark for Delhi Sales view.
• Add a Button → “Reset Filter”.

4️⃣1️⃣AI Visuals in Power BI

🔹 Types of AI Visuals
1. Q&A Visual → Ask questions in plain English.
2. Decomposition Tree → Break down a measure into components.
3. Key Influencers → Shows what drives a metric (e.g., Revenue affected by City).

🔹 Example
• Q&A → Ask: “Top 5 pizzas by revenue”.
 • Power BI builds chart automatically.

4️⃣2️⃣Designing for Phone vs Desktop Report

Viewers
🔹 Why Important?
Reports should be responsive on both desktop and mobile.

🔹 Steps
1. Go to View → Mobile Layout.
2. Drag visuals into phone-friendly layout.
3. Publish → Users on mobile see clean view.

4️⃣3️⃣Publishing Reports to Power BI Services

🔹 Steps
1. Click Publish in Power BI Desktop.
2. Select Power BI Service Workspace.
3. Report is available online → Can be shared with others.

4️⃣4️⃣Projects (1–4)
• Project 1 → Pizza Sales Dashboard (Revenue by Type, City).
• Project 2 → HR Dashboard (Employee Attrition, Salary Trends).
• Project 3 → Financial Dashboard (Revenue vs Expenses).
• Project 4 → Retail Sales Dashboard (Monthly & Product Sales Trends).

4️⃣5️⃣Career Guidance
• Roles after Power BI:
– Data Analyst
– Business Intelligence Developer
 – Power BI Developer
– Data Visualization Specialist
• Skills Needed: Power BI + SQL + Excel + Communication.

4️⃣6️⃣Resume, Portfolio Building & LinkedIn

Optimization
• Build a portfolio of dashboards (HR, Sales, Finance).
• Showcase on GitHub / LinkedIn.
• Highlight Power BI, SQL, DAX, Data Modeling in resume.

4️⃣7️⃣Interview Preparation
• Common Questions:
– Difference between Calculated Column & Measure.
– Explain Relationships & Cardinality.
– How does CALCULATE work?
– Difference between DirectQuery & Import Mode.
– How to optimize performance in Power BI?
• Tips:
– Always explain with examples.
– Focus on data modeling and DAX.
– Be ready to demo a dashboard.

✅ That completes all topics from your caption file with notes, steps, and examples.

Do you want me to now organize these notes into a proper PDF/Word file for study revision?

Perfect 👌 Since you’re preparing for Power BI interviews, here’s a list of 50 most important and
frequently asked questions with answers. I’ll cover basic, intermediate, and advanced levels.
🔥 Top 50 Power BI Interview Questions &
Answers

A. Basics of Power BI (Q1–Q15)

Q1. What is Power BI? Ans: Power BI is a business intelligence tool by Microsoft used to connect
to data sources, transform data, build data models, and create interactive visual reports &
dashboards.

Q2. What are the components of Power BI?

• Power BI Desktop → report creation

• Power BI Service → cloud-based sharing
• Power BI Mobile → mobile app
• Power BI Gateway → connects on-premises data
• Power BI Report Server → on-premises reporting

Q3. Difference between Power BI Desktop and Power BI Service?

• Desktop → Development tool for building reports.

• Service → Online platform to publish, share, and collaborate.

Q4. What are the data connectivity modes in Power BI?

• Import Mode → Data stored in PBIX file (faster, good for small data).
• DirectQuery → Live connection to database (real-time, but slower).
• Live Connection → Directly connects to Analysis Services / SSAS.

Q5. What is Power Query? Power Query is a data connection and transformation tool in Power
BI (ETL). Used to clean, shape, and prepare data before loading into the model.

Q6. What are Filters in Power BI?

• Visual-level filter → Applied to one chart.

• Page-level filter → Applied to entire page.
• Report-level filter → Applied to entire report.

Q7. What is a Slicer? A Slicer is a visual filter (like buttons or dropdowns) that allows interactive
filtering across multiple visuals.
Q8. Difference between Table and Matrix in Power BI?

• Table → Shows data in rows & columns (like Excel).

• Matrix → Pivot-table style (rows, columns, measures, drill-down).

Q9. What are Calculated Columns in Power BI? Columns created using DAX formulas and
stored in the data model. They consume storage.

Q10. What are Measures in Power BI? Calculated values created using DAX. They are computed
on the fly and don’t consume storage.

Q11. Difference between Calculated Column and Measure?

• Calculated Column → Row by row calculation, stored in memory.

• Measure → Calculated at query time, lightweight, recommended.

Q12. What is the role of Relationships in Power BI? Relationships connect tables using Primary
Key & Foreign Key for accurate data modeling.

Q13. What is Cardinality in Power BI? Cardinality = Type of relationship between two tables.

• One-to-Many (1:), Many-to-One (:1), One-to-One (1:1), Many-to-Many (:).

Q14. Difference between Power BI and Excel?

• Excel → Spreadsheet with pivot tables.

• Power BI → Specialized BI tool with advanced visualization, data modeling, automation.

Q15. What is DAX? DAX (Data Analysis Expressions) is a formula language in Power BI used for
calculated columns, measures, and data modeling.

B. DAX & Data Modeling (Q16–Q30)

Q16. What is the difference between SUM and SUMX?

• SUM(Column) → Adds column values directly.

• SUMX(Table, Expression) → Row by row evaluation, then adds result.
Q17. What is CALCULATE in Power BI? CALCULATE modifies the filter context of an expression.

Delhi Sales = CALCULATE(SUM(Sales[Amount]), Customers[City] = "Delhi")

Q18. What is the difference between ALL and REMOVEFILTERS in DAX?

• ALL → Removes all filters from a table/column.

• REMOVEFILTERS → Similar to ALL but newer; works better with complex filters.

Q19. What are Time Intelligence Functions in DAX? Functions used to calculate YTD, MTD,
QTD, and YoY growth. Examples: TOTALYTD(), SAMEPERIODLASTYEAR(), DATEADD().

Q20. What is a KPI Visual in Power BI? KPI (Key Performance Indicator) compares a measure
with a target and shows performance trend.

Q21. Difference between SUM, COUNT, DISTINCTCOUNT?

• SUM → Adds values.

• COUNT → Counts rows with non-empty values.
• DISTINCTCOUNT → Counts unique values.

Q22. What is the difference between Row Context and Filter Context in DAX?

• Row Context → Evaluates formula row by row.

• Filter Context → Context applied by filters, slicers, relationships.

Q23. Explain RANKX in DAX. Ranks items based on a measure.

Customer Rank = RANKX(ALL(Customers), [Total Revenue], , DESC)

Q24. What is the difference between RELATED and RELATEDTABLE?

• RELATED(Column) → Fetches column from related table.

• RELATEDTABLE(Table) → Returns table of related rows.

Q25. Difference between EARLIER() and EARLIEST()?

 • EARLIER() → Refers to outer row context.
• EARLIEST() → Refers to the first outer row context in nested calculations.

Q26. What is a Star Schema in Power BI? Data model design with Fact tables (sales, revenue)
and Dimension tables (customer, product, date).

Q27. What is a Snowflake Schema? Extension of Star Schema where dimension tables are
normalized into multiple related tables.

Q28. What is USERELATIONSHIP in DAX? Activates an inactive relationship in the data model.

Q29. What is the difference between DirectQuery and Import Mode?

• Import → Data stored in Power BI, faster.

• DirectQuery → Data stays in DB, real-time, but slower.

Q30. What is a Composite Model? Model that uses both Import Mode and DirectQuery
together.

C. Visualization & Performance (Q31–Q45)

Q31. What are Bookmarks in Power BI? Bookmarks save a snapshot of the current report view
(filters, visuals). Used for storytelling.

Q32. What are Buttons in Power BI? Interactive elements for navigation (Go to Page, Reset
Filters, Drill-through).

Q33. What is Drill-through in Power BI? Allows users to right-click a data point and navigate to
a detailed page about that context.

Q34. What is Drill-down in Power BI? Ability to click a visual (like a bar chart) and see deeper
levels (Year → Month → Day).

Q35. What is the difference between Pie Chart and Donut Chart in Power BI?
 • Pie Chart → Single circle with slices.
• Donut Chart → Same as pie, but with a hole in the center.

Q36. What is a TreeMap in Power BI? Hierarchical chart where categories are represented as
rectangles sized by value.

Q37. What is a Decomposition Tree in Power BI? AI visual that breaks down a measure (e.g.,
Revenue → by Region → by Product).

Q38. What is a Key Influencer Visual in Power BI? AI visual that explains which factors influence
a metric (e.g., “Sales influenced by City = Delhi”).

Q39. What is a Q&A Visual in Power BI? Allows users to type natural language queries like:
“Show top 5 products by revenue.”

Q40. How do you handle NULL values in Power BI?

• Use Power Query → Replace Nulls with 0 or Blank.

• Use DAX → IF(ISBLANK([Measure]), 0, [Measure]).

Q41. How do you optimize performance in Power BI?

• Use Star Schema.

• Avoid unnecessary columns.
• Use measures instead of calculated columns.
• Use aggregations and summaries.
• Optimize DAX with variables.

Q42. What is Row-level Security (RLS)? Feature that restricts data for specific users. Example:
Delhi manager sees only Delhi sales.

Q43. Difference between Static RLS and Dynamic RLS?

• Static RLS → Fixed filter (e.g., City = Delhi).

• Dynamic RLS → Uses user’s login/email to filter data dynamically.

Q44. How do you schedule data refresh in Power BI Service?

 • Publish dataset → Go to Dataset Settings → Schedule Refresh → Set frequency
(daily/hourly).

Q45. What are Power BI Gateway types?

• Personal Gateway → For individual use.

• Enterprise Gateway → For teams, multiple users.

D. Career & Practical (Q46–Q50)

Q46. What are common Power BI job roles?

• Data Analyst, BI Developer, Power BI Developer, Data Engineer, Reporting Analyst.

Q47. What projects can you showcase in Power BI portfolio?

• Sales Dashboard, HR Attrition Dashboard, Financial Performance Dashboard, Retail

Analysis.

Q48. What are common mistakes beginners make in Power BI?

• Using too many calculated columns.

• Not building a proper star schema.
• Overloading visuals in one page.
• Ignoring performance optimization.

Q49. What are best practices for dashboard design?

• Keep it simple and clean.

• Use KPIs at top (Revenue, Profit, Customers).
• Use consistent colors.
• Provide filters/slicers.
• Ensure mobile-friendly design.

Q50. How should you prepare for a Power BI interview?

• Learn DAX (SUMX, CALCULATE, FILTER).

• Practice Data Modeling (Star Schema).
• Build 2–3 projects (Sales, HR, Finance).
• Revise visualization features (Slicers, KPI, Bookmarks).
• Be ready with portfolio & GitHub/LinkedIn showcase.
✅ These 50 Q&As cover theory + practical + real interview scenarios.

Do you want me to now make a PDF of these Q&As so you can use it like an interview booklet?

Yes 👍 You are probably referring to Desktop Query in Power BI, which is actually Power Query
in Power BI Desktop. Let me explain clearly 👇

🔹 Power Query in Power BI Desktop

✅ What is Power Query?
Power Query is a data transformation engine inside Power BI Desktop (also available in Excel).

• It is used to connect, clean, shape, and transform data before loading it into the data
model.
• It follows the ETL (Extract, Transform, Load) process.

👉 So when someone says Desktop Query, they usually mean Power Query Editor in Power BI
Desktop.

✅ Where is it Found?
1. Open Power BI Desktop.
2. Click on Home → Transform Data.
3. This opens the Power Query Editor.

✅ Features of Power Query (Desktop Query)

1. Data Connectivity
– Connect to Excel, SQL Server, Web, APIs, CSV, JSON, etc.
2. Data Cleaning
– Remove nulls, blanks, duplicates.
– Replace values.
– Handle errors.
3. Transformations
– Split/merge columns.
– Change data types.
– Unpivot/Pivot.
– Group data.
 4. Combine Data
– Merge Queries (like SQL Join).
– Append Queries (like SQL Union).
5. Applied Steps
– Every transformation step is recorded automatically (M code).
– Can undo/redo steps anytime.

✅ Example Use Case

Imagine you import Pizza Sales Data (CSV):

• Some columns have null values.

• Date is in text format.
• Revenue column has currency symbols.

Using Power Query in Desktop:

1. Remove null rows.

2. Change Date column type to Date.
3. Remove currency symbol from Revenue & convert to Decimal.
4. Merge Customer Table with Orders Table.
5. Load cleaned data into Power BI Model.

✅ Difference: Power Query vs DAX

Power Query (Desktop Query) DAX
Used before loading data (Data Prep). Used after loading data (Calculations).
Row-level transformations. Aggregations, measures, advanced calculations.
Written in M Language. Written in DAX Language.

👉 So yes, there is no concept officially called Desktop Query, but what people mean is Power
Query inside Power BI Desktop.

Would you like me to create detailed step-by-step notes on Power Query transformations
(with examples) so you can keep them for interview prep?