Date: Monday, 28/07/2025 Session: Introduction to Power BI
Power BI is a business intelligence and data visualization tool by Microsoft.
It helps convert raw data into interactive dashboards and reports, allowing businesses to make data-driven decisions.
Key highlights of Power BI:
- Can connect to multiple data sources (Excel, SQL, Web APIs, etc.).
- Provides drag-and-drop visualization features.
- Allows data cleaning and transformation without heavy coding.
- Enables sharing dashboards and reports on Power BI Service (cloud).
Use cases of Power BI:
- Analyzing sales and financial data.
- Creating company-wide performance dashboards.
- Reporting KPIs and operational metrics.
To get started, we need Power BI Desktop:
- Download Power BI Desktop from:
- Install the application like any Windows program.
- First look after installation:
- Report View (Canvas): Where you design dashboards.
- Data View: Shows raw data tables after loading.
- Model View: Displays relationships between tables.
Power BI is broadly divided into three main sections for workflow:
- Purpose: Extract, Transform, Load (ETL) data before analysis.
- Common operations:
- Removing duplicates
- Splitting columns
- Filtering rows
- Changing data types
- Merging and appending datasets
- Interface: Called Power Query Editor, opens in a separate window.
- Purpose: Define relationships between tables and build a logical model for reporting.
- Key points:
- Create one-to-many and many-to-many relationships.
- Set primary and foreign keys for relational modeling.
- Hide unnecessary columns to simplify reporting.
- Optimize model for performance and simplicity.
- Purpose: Create calculated columns, measures, and KPIs.
- DAX is similar to Excel formulas but designed for data aggregation and modeling.
- Examples:
SUM(Sales[Revenue])→ Sum of revenue column.CALCULATE(SUM(Sales[Revenue]), Region="West")→ Revenue for West region only.
- Use the Get Data button on the Home ribbon.
- Popular data sources:
- Flat files: Excel, CSV, JSON
- Databases: SQL Server, MySQL, PostgreSQL
- Online services: SharePoint, Google Analytics, Web APIs
- After selecting a source:
- Connect to the file or server.
- Preview data in Navigator Window.
- Load data directly or Transform Data in Power Query.
During the session, we performed basic data cleaning using Power Query:
-
Renaming Columns:
Makes the dataset more readable for reporting. -
Changing Data Types:
Example: Convert a column from Text to Date for time-based analysis. -
Removing Unnecessary Columns:
Helps reduce clutter and memory usage. -
Filtering and Sorting:
Only keep relevant rows and order data logically. -
Handling Missing Data:
Replace nulls, remove blanks, or create default values. -
Removing Duplicates:
Ensures accuracy in reporting, especially for sales or transactional data.
Note: All operations in Power Query are recorded as steps and can be modified or removed anytime.
Make sure you have AdventureWorks_Database.csv and Budget.csv files present in your local system, you can download them from this repositary
Step 2 : Connect to Excel Workbook option and choose AdventureWorks_Database.csv from your local machine
Review the relationships between tables and their connections to each other, PowerBI Auto Models the Dataset for you, but you can manually model your dataset through common Primary Key eg : Product Key and Dragging the relationships between them
Step 5 : Select Get Data Again and select Budget.csv and then select Transform Data option this time
Applied Steps
Power Query Ribbon for operations
Table selection
We can see that first two rows are redudant and ought to be removed for clearning data purposes
Write 2 in the input box to remove those rows
For proper display purposes of the dataset, we can use Top Column Name as Headers, We can select
Scrolling to the Rightmost column Grand Total column is redudant for our purposes (it is derivable by summation of other columns)
Hence it can be removed by Right clicking on the column and removing it
Looking at the dataset we can see that subTotal Rows have null values which ought to be removed, hence we have to filter them out
in the Category Column we Select Text Filters Option -> Does not Contain to remove the redudant subTotal Rows
Step 13 : To Transform the Data to Convert from Column Years (Jan, Feb....) to Rows (Pivoting), Select The Year Columns and Press Shift Key to select all columns from Jan to Dec
Step 14 : Go to Transform ribbon icon and select Unpivot Columns to pivot the columns to rows (while keeping the year columns selected)
Step 15 : Rename Column Name after Transformation -> Double Click on Attribute to change to Date and Value -> BudgetAmount
Step 18 : Review the newly added Budget Table and Concept of Data Tables and ** Lookup Tables** in relation to these data sets
During the session, we:
- Imported dataset using Get Data.
- Opened Power Query Editor for cleaning.
- Performed transformations:
- Removed duplicates and null values.
- Renamed columns for clarity.
- Changed column data types.
- Closed and Applied the cleaned dataset to Power BI.
- Explore Data Modeling and create table relationships.
- Learn basic DAX functions for calculated columns and measures.
- Begin creating visual dashboards with charts and slicers.
PowerBI Session 2 will consist of Advanced Data Visualisation techquines, Dax Measures and Forecasting
Step 2 : Get Data -> Text/csv Data source and select SuperStore.csv from your local machine, make sure to click on Transform Data to open in Power Query Editor
Step 4 : Quick check the columns by doing Ctrl + A to select all columns then doing to View and selecting Column Quality
Select the Returns Column and Right-Click to select the Replace Values Option
Slect the last 2 columns and Right Click to Remove Columns
Step 8 : in the Report View, You can see all the columns in the Data Section as well as the Superstore Table and all its corrosponding Columns
X-Axis -> Category
Y-Axis -> Sales
Step 10 : Change background by selecting Format your report Page [Click anywhere on the canvas] and Canvas Background to select background gradient
Step 11 : Select on the Clustered Bar Chart, then Format Your Visual to change the visualisation formatting
General -> Effects -> Change Background Transperancy [85%]
General -> Effects -> Visual Border ->
Change Border Color, Rounded Borders
General -> Title -> Text :
Sales By Category
Horizontal Alignment -> Center
Visual -> Y- axis -> Values : Color change, Maximum Width
Visual -> X-axis -> Values : off
Visual -> Data Label : ON
Visual -> Bars to change color
Copy Paste the Sales By Category Bar chart
Edit the Y-axis in the Visualisation Tab to add Sub-Category in Y-axis and remove Category
Filtering : Select Filter Type -> Top N -> 3
By Value -> Drag and Drop Sales from Data and apply filters
X-axis -> Order Date
Y-axis -> Sales
Make sure to add Order Date in Legend and Right-Click to select Date Hierarchy
Remove Year, Quator from X-axis
File -> Options and Settings -> Options -> Security
Select OK and Restart PowerBI
Add Sales to the Bubblesize
Add Profits to the ToolTips
You can copy the format using Format Painter and change the Style of Map in Map Settings in Visualisation
Legend -> Segment
Values -> Sales
Add Slicer From Visualization Tab, Then aadd Region to the fields, change the Format to Tiles
Add Card
Then Add Fields, Sales, Quantity, Profit
for each card
AvgDelivery = DATEDIFF('SuperStore_Sales_Dataset'[Order Date], 'SuperStore_Sales_Dataset'[Ship Date], DAY)
Click Enter to populate the New Column
Add Card in the Report View to add the Average Delivery Time
Measure : TotalDelivery = COUNTROWS('SuperStore_Sales_Dataset')
Measure : LateDelivers = CALCULATE(COUNTROWS('SuperStore_Sales_Dataset'), DATEDIFF('SuperStore_Sales_Dataset'[Order Date], 'SuperStore_Sales_Dataset'[Ship Date], DAY) > 5)
Measure : ReturnRate = DIVIDE( CALCULATE(COUNTROWS('SuperStore_Sales_Dataset'), 'SuperStore_Sales_Dataset'[Returns] = "1"), CALCULATE(COUNTROWS('SuperStore_Sales_Dataset')) )
Forecasting can be done only on Line Chart
X-Axis -> Order Date Y-Axis -> Sales
Query : SalesForecast = SUMMARIZE('SuperStore_Sales_Dataset', SuperStore_Sales_Dataset[Order Date], "Total Sales", SUM(SuperStore_Sales_Dataset[Sales]))
In Power BI Session 2, we focused on advanced visualization techniques, DAX measures, and forecasting to derive actionable insights from the SuperStore.csv dataset.
- Replaced incorrect values in the
Returnscolumn (e.g., changed text "N/A" to "0" or "1") - Checked column quality and data types
- Removed extra/empty columns
- Clustered Bar Charts for:
- Sales by Category
- Top 3 Sub-Categories by Sales
- Sales by Ship Mode
- Stacked Area Chart for:
- Monthly Sales Comparison
- Year-over-Year (YoY) Profit Analysis
- Donut Charts for:
- Sales by Segment
- Payment Modes
- Map Visuals for:
- Sales by State (Bubble Size)
- Profit (as Tooltip)
- Slicers:
- Region Filter (Formatted as Tiles)
- KPI Cards:
- Total Sales
- Total Profit
- Total Quantity
- Visual Styling:
- Background gradients, rounded borders, transparency
- Customized axes, bar colors, titles, and alignment
AvgDelivery– Average delivery time between order and ship dateTotalDelivery– Total number of deliveriesLateDelivers– Deliveries taking more than 5 daysReturnRate– Percentage of returned orders per product
ReturnRate =
DIVIDE(
CALCULATE(COUNTROWS('SuperStore_Sales_Dataset'), 'SuperStore_Sales_Dataset'[Returns] = "1"),
CALCULATE(COUNTROWS('SuperStore_Sales_Dataset'))
)
- Created Line Chart using:
- X-Axis:
Order Date - Y-Axis:
Sales
- X-Axis:
- Enabled Forecasting via the analytics pane
- Configured:
- Confidence interval
- Forecast length (e.g., 10 days)
- Removed year/month hierarchy to allow daily forecast
- Enabled zoom slider and timeline navigation
Created a new table to summarize sales by order date:
SalesForecast =
SUMMARIZE(
'SuperStore_Sales_Dataset',
'SuperStore_Sales_Dataset'[Order Date],
"Total Sales",
SUM('SuperStore_Sales_Dataset'[Sales])
)
Website: https://data-science-and-analytics-club.github.io/PowerBI-Datasets/
- Choose one dataset that you understand well, especially the meaning of each column.
- Some datasets may be unclean or annoying to deal with — this is intentional to simulate real-world data challenges.
- Load data into Power Query Editor before working on it in Power BI.
- Check data formats:
- Ensure date columns are actually set as Date type.
- Handle empty values:
- Use Transform → Replace Values to replace empty cells with
nullor any other appropriate placeholder.
- Use Transform → Replace Values to replace empty cells with
- Remove redundant columns:
- Delete columns like indexes or any that do not add value to your analysis.
- Identify the X-axis and Y-axis you need for each question.
- Use the Filter section to:
- Apply Top N filtering
- Apply basic filters
- Combine multiple filtering rules
- Add data labels to improve readability.
- Choose the right graph type:
- Bar chart
- Pie chart
- Area chart
- Line graph
- Other charts depending on the insight you want to show.
- You can use notes, online resources, or any reference material to help solve questions.
- Tackle problems head-on — some questions will be intentionally harder and might require exploring concepts not yet taught.
- Use this as an opportunity to explore and learn independently.
- Go beyond the given questions:
- Define and display basic KPIs (Key Performance Indicators) to understand your dataset better.
- Example KPIs: Total Sales, Average Price, Number of Items Sold, Customer Count, etc.
- Look for patterns, anomalies, and trends in your dataset.
Prepared by Yash Baviskar
Power BI Roadmap - Session 1 and 2