Data Analytics-
Power-BI
Dr. Quratulain
Burhan
Data Quick Demo
Introduction to Data Loading in Power BI
• Power BI supports loading data from multiple sources.
Data Quick Demo
Introduction to Data Loading in Power BI
• Open Power BI Desktop:
• Navigate to the "Home" tab and select "Get Data.
• Choose Your Data Source: Examples: Excel, SQL Server, or other
database sources.
• Connect to the Source:
• Select Specific Tables:
• In the Navigator window, avoid selecting full sheets.
• Expand the sheets and select only the desired tables.
Data Quick Demo
Benefits of Loading Tables Only
• Reduces unnecessary data import.
• Improves data refresh speed and efficiency.
• Provides more control over data cleaning and transformations.
Data Quick Demo
Loading Data
• Reduces unnecessary data import.
• Improves data refresh speed and efficiency.
• Provides more control over data cleaning and transformations.
Merge and Append
Aspect Merge Append
Combines two datasets Adds rows from one dataset
Definition
based on a common key(s). to another.
Works on datasets with Datasets must have the
Structure
shared keys (e.g., IDs). same columns or structure.
Combines rows horizontally
Output Combines rows vertically.
by joining columns.
Key
Requires matching key No key is required; matches
Depende
columns or indices. by column names.
ncy
Data Quick Demo
Why Append Tables?
Combine multiple tables with the same structure into a single dataset.
Useful for consolidating monthly, regional, or departmental data.
Steps to Append Tables:
Load all the similar tables separately (2015 Sales, 2016 Sales, 2017 Sales)
Go to "Home" → "Transform Data" to open Power Query Editor.
Select "Append Queries" → Choose "Append Queries as New."
Select the tables to append and click OK.
Data Quick Demo
When and Why to Use Append
Use Cases:
1. Combining Period Data:
1. Adding new rows from the same type of data (e.g., monthly sales data).
2. Example: Appending Q2 data to Q1 data for yearly analysis.
2. Expanding Observations:
1. When data is split across multiple files or batches.
2. Example: Survey results collected in chunks.
3. Data Aggregation:
1. Combining datasets with the same structure to create a larger dataset.
Why Use Append:
• Quickly increases the size of the dataset for vertical analysis.
• Easy to use when column names match perfectly.
• Efficient for aggregating time-series data or survey responses.
Data Quick Demo
Assumptions for Append Table
• Ensure tables have the same column names and data types
before appending.
• Clean data (e.g., remove duplicates) before appending to maintain
data integrity.
• Regularly refresh the appended dataset to include updates in
source tables.
Exercise
1. Average Revenue
2. Maximum Revenue
3. Minimum Revenue
Exercise (Cookies Data)
Sales Overview
Component: Total Sales
• Exercise: Calculate and display the total sales amount.
• Tool: Use a card or KPI visual.
Component: Total Boxes Shipped
• Exercise: Count and display the total number of boxes shipped.
Exercise (Cookies Data)
Sales Trend
Component: Sales Over Time
• Exercise: Create a line chart to show sales amount trends over time.
• Tool: Plot "Date" on the x-axis and "Amount" on the y-axis.
Exercise (Cookies Data)
Geographic Performance
Component: Sales by Country
• Tool: Use "Country" and "Amount."
Component: Country Performance
• Exercise: Use a bar chart to rank countries based on total sales.
Exercise (Cookies Data)
Product Analysis
Component: Product-wise Sales
• Exercise: Create a bar or pie chart to show sales distribution by product.
• Tool: Use "Product" and "Amount."
Component: Product Performance Table
• Exercise: Create a table showing "Product," "Total Sales," and "Boxes
Shipped."
Exercise (Cookies Data)
Salesperson Performance
Component: Top Salespersons
• Exercise: Use a bar chart to display the top-performing salespersons based
on total sales.
• Tool: Use "Sales Person" and "Amount."
Component: Salesperson Contribution
• Exercise: Use a pie chart to show the percentage contribution of each
salesperson.
Sample Dashboard(Cookies
Data)
