Power Query (ETL)

5. Power Query — Data Transformation (ETL)

Power Query is where you clean and transform data before it enters the model. It uses a language called M.

Common Transformations

Operation	What It Does	How
Remove columns	Drop unnecessary columns	Right-click → Remove
Filter rows	Remove irrelevant rows	Dropdown → filter conditions
Change data type	Ensure correct types	Click column header icon
Split column	Split "John Smith" into "John" and "Smith"	Right-click → Split by delimiter
Unpivot columns	Convert wide format to tall format	Select columns → Unpivot
Merge queries	JOIN two tables (like SQL JOIN)	Home → Merge Queries
Append queries	UNION two tables (stack rows)	Home → Append Queries
Add custom column	Create a new calculated column	Add Column → Custom Column
Group By	Aggregate data	Home → Group By
Replace values	Find and replace in a column	Right-click → Replace Values

Merge vs Append

Operation	SQL Equivalent	What It Does
Merge	JOIN	Combines columns from two tables based on a matching key
Append	UNION	Stacks rows from two tables with the same structure

🧠 Merge = side by side (columns add hote hain). Append = upar neeche (rows add hote hain). SQL ke JOIN vs UNION jaisa.

---

6. Visualization Best Practices in Power BI

Chart Selection Guide

Scenario	Recommended Visual
Single KPI number	Card or KPI visual
Compare categories	Bar/Column chart
Trend over time	Line chart
Part of whole	Donut/Pie (max 5 categories)
Geographic data	Map or Filled Map
Detailed data	Table or Matrix
Multiple KPIs + sparklines	Matrix with conditional formatting
Categories with hierarchy	Treemap
Target vs Actual	Gauge or Bullet chart

Report Design Rules

1. KPI cards at the top — the first thing viewers see
2. Consistent color palette — use 3-4 colors max
3. Slicers on the left or top — filters should be accessible but not dominating
4. Title every visual — descriptive, not generic ("Revenue by Region FY24" not just "Chart")
5. Use bookmarks for multiple views within one report
6. Enable drill-through for detailed analysis pages
7. Phone layout — always create a mobile-friendly version

---

7. Row Level Security (RLS)

RLS restricts data access based on the logged-in user. For example, a regional manager sees only their region's data.

-- DAX filter for RLS role:
[Region] = USERPRINCIPALNAME()
-- Or static role: [Region] = "North"

Steps:

1. Define roles in Power BI Desktop (Modeling → Manage Roles)
2. Add DAX filters to each role
3. Assign users to roles in Power BI Service

---