Power BI is a fantastic tool for data analysis and visualization.
Let's explore some basic
operations with simple, file-based examples. We'll focus on getting data from common
file types, doing some basic transformations, and creating a simple visualization.
These examples cover the very fundamental operations: importing data from files,
performing basic transformations in Power Query, merging data, creating calculated
columns, and building simple visualizations. Power BI's capabilities extend far beyond
this, but mastering these basics is a great starting point!
Example 1: Importing and Visualizing Data from a CSV File
Scenario: You have a CSV file containing sales data and want to visualize total sales by
region.
1. Prepare your Data (CSV File):
Create a simple text file named sales_data.csv with the following content:
Code snippet
Date,Region,Product,Sales
2024-01-01,North,Laptop,1200
2024-01-01,South,Mouse,50
2024-01-02,North,Keyboard,75
2024-01-02,East,Laptop,1500
2024-01-03,South,Monitor,300
2024-01-03,West,Mouse,45
2024-01-04,North,Laptop,1300
2024-01-04,East,Keyboard,80
Save this file somewhere accessible on your computer (e.g., your Desktop or a "Power BI
Examples" folder).
2. Get Data into Power BI Desktop:
• Open Power BI Desktop.
• In the "Home" tab, click on "Get data".
• Select "Text/CSV".
• Navigate to where you saved sales_data.csv, select it, and click "Open".
• A preview window will appear. Power BI will usually detect the delimiter and data
types correctly. Click "Load".
o Note on "Transform Data": If your data needed cleaning (e.g., changing
data types, removing columns, filtering rows), you would click "Transform
Data" to open the Power Query Editor. For this simple example, we're
skipping that, but it's where much of the data preparation happens.
3. Create a Simple Visualization:
• Once loaded, you'll see your sales_data table in the "Fields" pane on the right.
• In the "Visualizations" pane, select the "Clustered Column Chart" icon.
• From the "Fields" pane, drag "Region" to the "X-axis" well.
• Drag "Sales" to the "Y-axis" well.
You should now see a bar chart showing the total sales for each region!
Example 2: Importing and Transforming Data from an Excel File
Scenario: You have an Excel file with product information, and you want to clean up
some data and then show the count of products by category.
1. Prepare your Data (Excel File):
Create an Excel file named product_data.xlsx with a sheet named Products and the
following content:
Product Name Category Price Status
Laptop X Electronics 1200 Active
Keyboard Y Electronics 75 active
Desk Z Furniture 250 Inactive
Chair A Furniture 150 ACTIVE
Mouse B Electronics 50 Active
Lamp C Home 30 active
Export to Sheets
Save this file.
2. Get Data into Power BI Desktop (with Transformation):
• Open Power BI Desktop.
• Click "Get data" > "Excel workbook".
• Navigate to and select product_data.xlsx, then click "Open".
• In the Navigator window, select the "Products" sheet.
• This time, click "Transform Data". This will open the Power Query Editor.
3. Basic Data Transformation in Power Query Editor:
• Standardize "Category" Case: Notice "Electronics" has different casing. Right-
click on the "Category" column header, go to "Transform", and select "Capitalize
Each Word". (You could also use "Uppercase" or "Lowercase" depending on your
needs).
• Standardize "Status" Case: Similarly, right-click on the "Status" column header,
go to "Transform", and select "Capitalize Each Word". This will make "active"
and "ACTIVE" into "Active".
• Change Data Type (Optional but good practice): Ensure "Price" is a decimal
number. If it's not, right-click on the "Price" column header, go to "Change Type",
and select "Decimal Number".
• Once you're done with transformations, click "Close & Apply" from the "Home"
tab in the Power Query Editor. This will load the transformed data into Power BI
Desktop.
4. Create a Simple Visualization:
• From the "Fields" pane, you'll see your Products table.
• Select the "Donut Chart" visualization.
• Drag "Category" to the "Legend" well.
• Drag "Product Name" to the "Values" well. Power BI will automatically count
the distinct product names, giving you a count of products per category.
You'll see a donut chart showing the distribution of your products across different
categories.
Example 3: Merging Data from Two CSV Files (Simple Join)
Scenario: You have one CSV file with order details (OrderID, ProductID, Quantity) and
another with product details (ProductID, ProductName, UnitPrice). You want to combine
them to calculate total sales per product.
1. Prepare your Data (CSV Files):
Create orders.csv:
Code snippet
OrderID,ProductID,Quantity
101,P001,2
102,P002,1
103,P001,3
104,P003,1
Create products.csv:
Code snippet
ProductID,ProductName,UnitPrice
P001,Laptop,1200
P002,Mouse,50
P003,Keyboard,75
2. Get Data and Transform (Open Power Query Editor):
• Open Power BI Desktop.
• Click "Get data" > "Text/CSV", load orders.csv, and click "Transform Data".
• In Power Query Editor, click "New Source" > "Text/CSV", load products.csv.
Now you have two queries (tables) in your Power Query Editor: orders and products.
3. Merge Queries:
• Make sure the orders query is selected in the "Queries" pane on the left.
• In the "Home" tab, click "Merge Queries" (or "Merge Queries as New" if you
want to keep the original tables separate). Let's use "Merge Queries" for simplicity
here.
• The "Merge" dialog box appears:
o For the "Primary table for merge", orders should already be selected.
o For the "Table to merge with", select products.
o Click on the "ProductID" column header in both tables to select them as the
matching columns.
o For "Join Kind", keep "Left Outer (all from first, matching from
second)". This is a common join type.
o Click "OK".
• A new column named products will appear in your orders table. It contains table
values. Click the expand icon (two arrows pointing opposite directions) in the
products column header.
• Uncheck "Use original column name as prefix" (unless you want
products.ProductName, products.UnitPrice, etc.).
• Select "ProductName" and "UnitPrice". Click "OK".
Now, your orders table has ProductName and UnitPrice columns alongside OrderID,
ProductID, and Quantity.
4. Create a Custom Column for Total Sales:
• With the orders query still selected, go to the "Add Column" tab in Power Query
Editor.
• Click "Custom Column".
• In the "New column name" field, type Total Sales.
• In the "Custom column formula" box, type: [Quantity] * [UnitPrice]
• Click "OK".
You now have a Total Sales column!
5. Close & Apply and Visualize:
• Click "Close & Apply" from the "Home" tab.
• In Power BI Desktop, select the "Table" visualization.
• Drag ProductName, Quantity, UnitPrice, and Total Sales to the "Columns" well.
You can also create a "Clustered Column Chart" with ProductName on the X-axis
and Total Sales on the Y-axis to see sales per product.
Example 4: Grouping and Aggregating Data
Scenario: You have a list of customer transactions and want to find the total amount spent
by each customer.
1. Prepare your Data (CSV File):
Create a CSV file named transactions.csv with the following content:
Code snippet
CustomerID,TransactionID,Amount,Date
C001,T001,150.75,2024-06-01
C002,T002,25.00,2024-06-01
C001,T003,50.20,2024-06-02
C003,T004,100.00,2024-06-02
C002,T005,12.50,2024-06-03
C001,T006,200.00,2024-06-03
2. Get Data and Group in Power Query Editor:
• Open Power BI Desktop.
• Click "Get data" > "Text/CSV", load transactions.csv, and click "Transform
Data".
• In the Power Query Editor, select the "CustomerID" column.
• Go to the "Transform" tab and click "Group By".
• In the "Group By" dialog box:
o Group by: CustomerID (this should be pre-selected).
o New column name: Total Spent
o Operation: Sum
o Column: Amount
• Click "OK".
You'll see a new table with CustomerID and Total Spent.
3. Close & Apply and Visualize:
• Click "Close & Apply".
• In Power BI Desktop, select the "Table" visualization.
• Drag CustomerID and Total Spent to the "Columns" well. You can also use a "Bar
Chart" to visually compare spending across customers.
Example 5: Creating a Calculated Column (DAX)
Scenario: You have product sales data including Quantity and Unit Price, and you want
to calculate the Total Revenue for each line item.
1. Prepare your Data (CSV File):
Create a CSV file named product_sales.csv with the following content:
Code snippet
OrderID,Product,Quantity,UnitPrice
1,Laptop,2,1200
2,Mouse,5,15
3,Keyboard,1,75
4,Monitor,1,300
5,Webcam,3,40
2. Get Data into Power BI Desktop:
• Open Power BI Desktop.
• Click "Get data" > "Text/CSV", load product_sales.csv, and click "Load".
3. Create a Calculated Column:
• Once the data is loaded, go to the "Data" view (the table icon on the left sidebar).
• Select the product_sales table in the "Fields" pane.
• In the "Table tools" tab on the ribbon, click "New column".
• In the formula bar that appears, type the following DAX formula:
Code snippet
Total Revenue = 'product_sales'[Quantity] * 'product_sales'[UnitPrice]
• Press Enter.
A new column named "Total Revenue" will appear in your table, showing the calculated
revenue for each row.
4. Visualize the Data:
• Go back to the "Report" view (the bar chart icon on the left sidebar).
• Select a "Clustered Column Chart".
• Drag Product to the "X-axis" well.
• Drag Total Revenue to the "Y-axis" well.
Example 6: Unpivoting Data for Better Analysis
Scenario: You have sales data where each month is a separate column, but you want to
analyze sales over time by combining months into a single column. This is common when
data is provided in a "pivot" format.
1. Prepare your Data (Excel File):
Create an Excel file named monthly_sales.xlsx with a sheet named Sales and the following
content:
Region Jan Sales Feb Sales Mar Sales
North 100 120 150
South 80 90 110
East 130 140 160
Export to Sheets
2. Get Data and Unpivot in Power Query Editor:
• Open Power BI Desktop.
• Click "Get data" > "Excel workbook", load monthly_sales.xlsx, select the Sales
sheet, and click "Transform Data".
• In the Power Query Editor:
o Select the Jan Sales, Feb Sales, and Mar Sales columns. You can click the
first, hold Shift, and click the last to select multiple contiguous columns, or
hold Ctrl and click individual columns.
o Right-click on any of the selected column headers.
o Choose "Unpivot Columns".
You'll now have two new columns: "Attribute" (which will contain "Jan Sales", "Feb
Sales", "Mar Sales") and "Value" (which will contain the sales figures).
• Rename Columns: Double-click on "Attribute" and rename it to Month. Double-
click on "Value" and rename it to Sales.
• Clean Month Column: Select the Month column, go to "Transform" tab, click
"Extract" > "Text after Delimiter". In the dialog, type (a space) as the delimiter.
Click "OK". This will remove " Sales" from the month names, leaving "Jan",
"Feb", "Mar".
3. Close & Apply and Visualize:
• Click "Close & Apply".
• In Power BI Desktop, select the "Line chart" visualization.
• Drag Month to the "X-axis" well.
• Drag Sales to the "Y-axis" well.
• Drag Region to the "Legend" well to see trends per region.
This transformation allows you to easily analyze sales trends over months, which would
be difficult with the original "wide" format.
Example 7: Basic Conditional Formatting
Scenario: You have a table of student scores and want to highlight scores above a certain
threshold (e.g., 80) with a green background.
1. Prepare your Data (CSV File):
Create a CSV file named student_scores.csv with the following content:
Code snippet
StudentName,Subject,Score
Alice,Math,85
Bob,Math,72
Charlie,Science,91
David,Science,68
Eve,Math,79
Frank,Science,95
2. Get Data into Power BI Desktop:
• Open Power BI Desktop.
• Click "Get data" > "Text/CSV", load student_scores.csv, and click "Load".
3. Apply Conditional Formatting:
• Once loaded, select the "Table" visualization.
• Drag StudentName, Subject, and Score to the "Columns" well.
• In the "Visualizations" pane, hover over the Score field in the "Columns" well of
your table visual.
• Click the down arrow that appears next to Score (or right-click Score).
• Select "Conditional formatting" > "Background color".
• In the "Background color" dialog box:
o For "Format style", select "Rules".
o For "What field should we base this on?", ensure Score is selected.
o Under "Rules":
▪ If value is greater than or equal to: 80 (select "Number" for the type)
▪ And is less than: 1000 (or a sufficiently high number, select
"Number")
▪ Color: Choose a green color.
▪ Click "Add a new rule".
▪ If value is greater than or equal to: 0 (select "Number")
▪ And is less than: 80 (select "Number")
▪ Color: Choose a light red or orange color.
• Click "OK".
Now your table will visually highlight scores based on your defined rules.
Example 8: Creating a Simple Date Hierarchy and Filtering
Scenario: You have daily transaction data and want to analyze sales by Year and Month.
1. Prepare your Data (CSV File):
Create a CSV file named daily_transactions.csv with the following content:
Code snippet
Date,Amount
2023-01-05,100
2023-01-15,120
2023-02-10,80
2023-02-20,110
2024-01-01,150
2024-01-10,200
2024-02-05,90
2. Get Data into Power BI Desktop:
• Open Power BI Desktop.
• Click "Get data" > "Text/CSV", load daily_transactions.csv, and click "Load".
• Important: Ensure the "Date" column is correctly recognized as a Date data type
(it usually is automatically, indicated by a calendar icon next to the field name in
the "Fields" pane). If not, go to "Transform Data", select the "Date" column, and
set its data type to "Date".
3. Utilize the Auto Date/Time Hierarchy:
Power BI automatically creates a date hierarchy for any column identified as a Date type.
• Select a "Clustered Column Chart" visualization.
• Drag the Date field to the "X-axis" well. Notice that Power BI automatically
expands it to a hierarchy (Year, Quarter, Month, Day).
• Drag Amount to the "Y-axis" well.
You can now click on the "Drill down" arrows in the visual header to navigate from Year
to Quarter, then Month, and Day.
4. Add a Slicer for Filtering by Year:
• Select the "Slicer" visualization.
• Drag the Date field to the "Field" well of the slicer.
• In the slicer visual, click the small down arrow next to the "Date Hierarchy" and
select "Year".
You now have a slicer that allows you to easily filter your chart by year.
