DEV Community: Pragnesh Pomal

Power BI

Pragnesh Pomal — Sun, 28 Jun 2026 17:20:36 +0000

What is Power BI?

Power BI is a business intelligence tool made by Microsoft. It lets you:

Connect to data (Excel, CSV, databases, websites, etc.)
Clean and transform that data
Build relationships between tables
Create interactive visual reports and dashboards
Share those reports with other people online

Step 1: Connecting to Data

The first thing you do in Power BI is connect to a data source. You go to:

Home → Get Data → choose your source

Sources we connected to in class:

Excel workbooks
CSV / text files
SQL Server databases
SharePoint lists
Web URLs (yes, you can pull data directly from a website!)

Once connected, Power BI opens Power Query Editor where the real work begins.

Step 2: Cleaning Data in Power Query

Power Query is Power BI's built-in data cleaning tool. This is where you transform your raw, messy data into something usable before loading it into your model.

Everything you do here is recorded as steps on the right side panel called Applied Steps. This means you can undo any step at any time without losing your work.

Transformations we learned:

Transform	What it does	When to use it
Remove Duplicates	Deletes repeated rows	Cleaning ID or key columns
Fill Down	Fills blank cells with the value above	Fixing merged cell exports from Excel
Unpivot Columns	Turns column headers into row values	Reshaping wide data into tall data
Merge Queries	Joins two tables together (like SQL JOIN)	Combining related tables
Split Column	Splits one column into two by a delimiter	Separating first and last names
Change Data Type	Sets the correct type (text, number, date)	Making sure dates are read as dates
Add Custom Column	Creates a new column using an M formula	Row-level calculations at query stage
Remove Rows	Removes top rows, blank rows, errors	Cleaning header junk from messy files

Step 3: Data Modeling

After cleaning your data, you move to the Model view. This is where you define how your tables relate to each other.

The Star Schema

The recommended structure for Power BI models is called a star schema:

One central Fact table ,this holds your transactions or events (sales, orders, payments)
Multiple Dimension tables around it, these describe the facts (customers, products, dates, regions)

Relationships go from the dimension table to the fact table on a one-to-many basis.

Types of relationships:

Cardinality	Meaning	Example
One-to-many (1:*)	One record in table A matches many in table B	One customer → many orders
Many-to-many (:)	Both sides have multiple matches	Students ↔ courses (needs a bridge table)
One-to-one (1:1)	Each record matches exactly one	Employee → employee detail table

Cross filter direction
Relationships have a filter direction, either Single or Both. Single means filters flow one way (from dimension to fact). Both means filters flow both ways. We were told to stick with Single unless you have a specific reason to change it.

Date Table

One big thing we learned, always create a dedicated Date table. Mark it as a Date Table in the model settings. DAX time intelligence functions absolutely require this to work correctly.

Step 4: DAX — The Formula Language

DAX stands for Data Analysis Expressions. It's the language used to write formulas in Power BI. It looks similar to Excel but behaves very differently.

The biggest mindset shift: in Excel you write formulas for individual cells. In DAX, you write formulas for entire columns or measures, and the filter context changes the result depending on where it's used in the report.

DAX functions we covered:

Basic aggregations:

SUM() — adds up a column
AVERAGE() — average of a column
COUNT() / COUNTROWS() — counts values or rows
MIN() / MAX() — smallest or largest value
DIVIDE() — safe division (handles divide by zero automatically)

Logical functions:

IF() — if/else logic
SWITCH() — cleaner alternative to nested IFs

Filter functions:

CALCULATE() — the most important DAX function. Evaluates an expression in a modified filter context
FILTER() — returns a filtered table
ALL() — removes filters from a column or table
ALLEXCEPT() — removes all filters except the ones you specify

Relationship functions:

RELATED() — pulls a value from a related table (like VLOOKUP)
RELATEDTABLE() — returns the related table

Time intelligence functions:

TOTALYTD() — year-to-date total
TOTALQTD() — quarter-to-date total

CALCULATE was the hardest one to understand. Basically, it lets you change the filter context of any measure. Once you get it, everything else makes sense.

Step 5: Building Visuals

This is the fun part! The Report view is a drag-and-drop canvas. You place visuals, connect them to your measures and dimensions, and build your report page.

Visual types we used:

Visual	Best used for
Bar / Column chart	Comparing categories
Line chart	Showing trends over time
Pie / Donut chart	Part-to-whole (max 5 slices!)
Card visual	Displaying a single KPI number
Matrix	Pivot-table style breakdowns with subtotals
Table	Showing detailed row-level data
Map	Plotting values geographically
Slicer	Filter controls that users click on the canvas
Scatter chart	Showing correlation between two measures
Waterfall chart	Showing how values build up or break down

Design rules our lecturer gave us:

One insight per visual, don't try to show everything in one chart
Maximum 5 to 6 visuals per report page
Always label your axes
Use slicers to let users filter the report themselves
Don't use pie charts with more than 5 slices, they become unreadable
Consistent colours across the whole report
If a stakeholder has to ask what they're looking at, the design has failed

Interactions between visuals

By default, clicking on one visual filters all the others on the page. You can customise this under Format → Edit interactions to control which visuals get filtered and which don't.

Step 6: Exporting / Downloading Your Table

Sometimes you need to get your cleaned data OUT of Power BI as a file.

Method 1 — From Power Query Editor:

Open Power Query Editor (Home → Transform data)
Select your table
Click File → Export → choose CSV
Save to your PC

Method 2 — From a Table Visual (most common):

Add a Table visual to your report canvas
Click the three dots (…) on the visual
Click Export data
Choose Underlying data → Export
A .xlsx or .csv downloads automatically

Method 3 — From Power BI Service:

Publish your report to app.powerbi.com
Open it in the browser
Hover over the visual → three dots (…) → Export data
Choose your format and download

Step 7: Publishing and Sharing

Once your report is done, you publish it to the Power BI Service.

Home → Publish → Select your Workspace

From the Service you can:

Create a Dashboard by pinning tiles from your report
Set a scheduled data refresh so your report stays up to date automatically
Share the report with colleagues by email or link
Embed the report into Microsoft Teams, SharePoint, or a website
Set up Row-Level Security (RLS) so different users see different data

Dashboard vs Report — I kept confusing these:

A Report has multiple pages, detailed visuals, full interactivity

A Dashboard is a single page of pinned tiles — a quick overview for monitoring KPIs

How Excel Is Used in Real-World Data Analysis

Pragnesh Pomal — Sat, 06 Jun 2026 12:35:49 +0000

When I first started learning Microsoft Excel, I thought it was just a tool for creating tables and doing calculations. After spending some time working with formulas, charts, and datasets, I realized that Excel is one of the most widely used tools for data analysis in the world.

According to Microsoft, Excel helps users organize, analyze, and visualize data, making it easier to understand information and make decisions.

Learn more: https://www.microsoft.com/microsoft-365/excel)

What Is Excel?

Microsoft Excel is a spreadsheet application that allows users to:

Store data
Perform calculations
Analyze information
Create charts and reports
Identify patterns and trends

Think of Excel as a digital notebook that can also perform thousands of calculations in seconds.

How Excel Is Used in the Real World

1.Business Decision-Making

Businesses collect large amounts of data every day.

For example:

Sales Data
    ↓
Analysis
    ↓
Identify Trends
    ↓
Business Decisions

A company can use Excel to:

Track monthly sales
Compare product performance
Forecast future demand
Monitor company growth

This helps managers make informed decisions instead of relying on guesswork.

2. Financial Reporting and Budgeting

Finance teams use Excel to manage money and prepare reports.

Example:

Income
  +
Expenses
  ↓
Profit/Loss Report

Excel makes it easier to:

Track expenses
Create budgets
Calculate profits
Monitor financial performance

Many small businesses rely heavily on Excel for day-to-day financial management.

3. Marketing Performance Analysis

Marketing teams use Excel to measure whether campaigns are successful.

Typical data includes:

Website visitors
Email open rates
Social media engagement
Sales conversions

Example process:

Marketing Campaign
        ↓
 Customer Data
        ↓
 Excel Analysis
        ↓
 Better Marketing Decisions

By analyzing this information, companies can improve future campaigns and spend their budgets more effectively._

Excel Features and Formulas I've Learned

SUM Function
The SUM function adds values together.

Example:

=SUM(B2:B10)

Useful for:

Total sales
Total expenses
Inventory counts

AVERAGE Function

The AVERAGE function calculates the mean value.

Example:

=AVERAGE(C2:C10)

Useful for:

Average sales
Average test scores
Average customer ratings

IF Function

The IF function performs a logical test.

Example:

=IF(D2>=50,"Pass","Fail")

Useful for:

Pass/fail results
Employee performance categories
Automated decision-making

Charts and Graphs

One feature I found especially useful is Excel's chart tools.

Example:

Sales Trend

Jan ████
Feb ██████
Mar █████████
Apr ███████████

Charts help transform numbers into visual stories that are much easier to understand.

My Personal Reflection

In my day-to-day work, I use Excel to create data sheets, organize information, and perform basic calculations. So far, I have mainly relied on simple functions and manual methods to complete my tasks. This has helped me manage data, but only at a basic level.

Through learning more about Excel, I have realized that I have only been using a small part of its capabilities. Excel has many useful features such as formulas, data analysis tools, and charts that can make working with data more efficient and accurate.

This learning experience has helped me understand that Excel is not just for storing information, but also for analyzing and interpreting data. I now see that it can help identify trends and improve decision-making in a more structured way.

Overall, I have learned that there is still a lot more to explore in Excel, and I am motivated to continue improving my skills so that I can use it more effectively in my work.

Conclusion

Excel is much more than a spreadsheet program. It is a powerful data analysis tool used in business, finance, marketing, education, and many other fields. Through functions, formulas, and visualization tools, Excel helps users transform raw data into meaningful information.

As I continue learning Excel, I am excited to explore more advanced features and develop stronger data analysis skills. This experience has shown me that understanding data is an important skill in today's digital world.