DAY 25: Integration Project: Excel + SQL + WEEK 5 TEST

Exercise 1: Connect Excel to SQL Database with Power Query

Goal: Learn to establish a connection from Excel to a SQL Server database and import data using Power Query.

Step-by-Step Instructions:

1. Open Excel and go to the Data tab
2. Click "Get Data" → "From Database" → "From SQL Server Database"
3. Enter connection details: - Server: localhost (or your SQL Server address) - Database: YourDatabaseName (optional, can select later)
4. Choose authentication method: - Windows Authentication (default for local SQL Server) - Or Database credentials (username/password)
5. Click OK and wait for the Navigator window to open
6. In Navigator, select your table: - Expand the database tree - Check the box next to "Customers" or "Orders" table - You'll see a preview on the right
7. Choose how to load: - Click "Load" to import directly to worksheet - Or "Transform Data" to open Power Query Editor for cleaning
8. Alternative: Use SQL query directly: - In Navigator, click "Advanced options" - Enter custom SQL query:

   SELECT c.CustomerID, c.Name, c.Region,
          SUM(o.Amount) AS TotalSales
   FROM Customers c
   LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
   WHERE o.OrderDate >= '2024-01-01'
   GROUP BY c.CustomerID, c.Name, c.Region

9. Click "Load" and the data appears in Excel!

💡 Tips:

• Power Query is non-destructive: Original database is never modified
• Connection is live: Right-click table → Refresh to get latest data
• Save connection for reuse: Connections appear in "Queries & Connections" pane
• Transform before loading: Clean data in Power Query before bringing to Excel
• Firewall issues: Ensure SQL Server allows remote connections (port 1433)

✅ Expected Result: Excel connected to SQL Server with data automatically imported and refreshable with one click

🔄 Connection to Previous Learning: Combines SQL queries (Days 2-24) with Excel analysis (Days 1-23)

Exercise 2: Transform and Clean SQL Data in Power Query

Goal: Master Power Query Editor to transform imported SQL data before loading into Excel.

Step-by-Step Instructions:

1. Import data from SQL Server (using Exercise 1 steps)
2. Instead of "Load", click "Transform Data" to open Power Query Editor
3. Basic transformations you can perform: - Filter rows: Click dropdown on column header → Filter values - Remove columns: Right-click column → Remove - Change data types: Click icon next to column name - Split columns: Right-click → Split Column → By Delimiter
4. Add calculated columns: - Go to "Add Column" tab - Click "Custom Column" - Name: "SalesCategory" - Formula: if [Sales] > 50000 then "High" else if [Sales] > 20000 then "Medium" else "Low"
5. Merge with another SQL table: - In Power Query, go to Home → Merge Queries - Select second table (e.g., Orders) - Select matching column (CustomerID) - Choose JOIN type (Left Outer, Inner, etc.) - Click OK and expand the merged column
6. Group and aggregate: - Go to Transform → Group By - Group by: Region - New column name: TotalSales - Operation: Sum - Column: Amount
7. Review all applied steps: See each transformation in "Applied Steps" pane on right
8. Click "Close & Load" to send clean data to Excel

💡 Tips:

• Every step is recorded: You can edit or delete any step in Applied Steps
• M language behind scenes: Power Query uses M language (can view in Advanced Editor)
• Refresh updates transformations: When you refresh, all transformations reapply automatically
• Merge is like JOIN: Power Query merge = SQL JOIN operation
• Append is like UNION: Use Append Queries to stack tables vertically
• Parameters for dynamic queries: Create parameters for date ranges, regions, etc.

✅ Expected Result: Clean, transformed data automatically refreshed from SQL with all transformations reapplied

🔄 Connection to Previous Learning: Power Query operations mirror SQL transformations (GROUP BY, JOIN, WHERE)

Exercise 3: Create Parameterized SQL Queries in Power Query

Goal: Build dynamic SQL queries that accept parameters from Excel, allowing users to filter data without editing queries.

Step-by-Step Instructions:

1. Create parameters in Power Query: - Open Power Query Editor - Go to Home → Manage Parameters → New Parameter - Name: StartDate - Type: Date/Time - Current Value: 1/1/2024
2. Create second parameter: - Name: SelectedRegion - Type: Text - Current Value: East
3. Create new query from SQL Server: - Get Data → SQL Server - Use Advanced options - Enter parameterized SQL query:

   SELECT *
   FROM Orders
   WHERE OrderDate >= '@StartDate'
     AND Region = '@SelectedRegion'

4. Wait - this won't work directly! We need to use M code: - In Power Query, go to Advanced Editor - Replace the SQL query with:

   let
       Source = Sql.Database("localhost", "YourDB"),
       Query = "SELECT * FROM Orders WHERE OrderDate >= '" & Date.ToText(StartDate, "yyyy-MM-dd") & "' AND Region = '" & SelectedRegion & "'",
       Result = Sql.Database("localhost", "YourDB"){[Schema="dbo",Item="Orders"]}[Data]
   in
       Result

5. Better approach - use native Power Query filtering: - Import full table from SQL - In Power Query, filter using parameters: - Filter OrderDate: = Table.SelectRows(Source, each [OrderDate] >= StartDate) - Filter Region: = Table.SelectRows(Source, each [Region] = SelectedRegion)
6. Create Excel interface for parameters: - In Excel, create cells: B1: StartDate, B2: Region - Link Power Query parameters to these cells (requires VBA or manual parameter update)
7. Refresh to apply new parameters

💡 Tips:

• Parameters make queries dynamic: Users change values without editing queries
• Use parameters for: Date ranges, regions, categories, thresholds
• SQL injection risk: Never concatenate user input directly into SQL queries
• Native Query folding: When possible, let Power Query push filters to SQL Server
• Test parameter changes: Manage Parameters → Edit values → Refresh to test
• List parameters for dropdowns: Create parameter with allowed values for user selection

✅ Expected Result: Dynamic Excel reports that update based on user-selected parameters (dates, regions, etc.)

🔄 Connection to Previous Learning: Parameterization similar to SQL stored procedures (Day 20) but accessible to Excel users

Exercise 4: Build Power Pivot Data Model with SQL Data

Goal: Create a Power Pivot data model with relationships between multiple SQL tables for advanced analysis.

Step-by-Step Instructions:

1. Enable Power Pivot (if not enabled): - File → Options → Add-ins - Manage: COM Add-ins → Go - Check "Microsoft Power Pivot for Excel" → OK
2. Import multiple tables from SQL: - Power Pivot tab → Manage → Get External Data → From Database → From SQL Server - Enter server details - Select multiple tables: - Customers - Orders - Products - OrderDetails
3. Click "Finish" to import all tables to Power Pivot
4. Create relationships: - In Power Pivot window, go to Diagram View - Drag CustomerID from Orders table to CustomerID in Customers table - A relationship line appears - Similarly: - OrderID in OrderDetails → OrderID in Orders - ProductID in OrderDetails → ProductID in Products
5. Create calculated columns: - In Data View, go to OrderDetails table - Add column: LineTotal = OrderDetails[Quantity] * OrderDetails[UnitPrice]
6. Create measures (DAX formulas): - Click in empty cell below data - TotalRevenue:=SUM(OrderDetails[LineTotal]) - TotalOrders:=COUNTROWS(Orders) - AverageOrderValue:=[TotalRevenue]/[TotalOrders] - CustomerCount:=DISTINCTCOUNT(Orders[CustomerID])
7. Use model in PivotTable: - Back in Excel: Insert → PivotTable - Use "Data Model" - Drag fields from multiple tables: - Rows: Customers[Region] - Values: [TotalRevenue], [CustomerCount]

💡 Tips:

• Power Pivot = Excel's database engine: Handles millions of rows efficiently
• Relationships are like JOINs: But automatic in PivotTables once defined
• DAX vs Excel formulas: DAX works on entire tables/columns, not individual cells
• Measures vs Calculated Columns: Measures aggregate data, columns compute row-by-row
• Data Model advantages: No VLOOKUP needed, relationships handle lookups automatically
• Memory-based: Power Pivot compresses and stores data in memory for speed

✅ Expected Result: Powerful data model allowing complex analysis across multiple SQL tables without complex formulas

🔄 Connection to Previous Learning: Combines SQL relationships (Day 6 JOINs) with Excel Power Pivot (Day 17)

Exercise 5: Create Automated Executive Dashboard with SQL Data

Goal: Build a complete automated dashboard that pulls SQL data and updates with one click.

Step-by-Step Instructions:

1. Set up data connections: - Use Power Query to import: - Sales data (with transformations from Exercise 2) - Customer data - Product data - Load all to Power Pivot Data Model
2. Create dashboard layout on new sheet:

   Cell A1: "Executive Sales Dashboard"
   Cell A3: "Refresh Date: =TODAY()"
   Cell E3: "Total Revenue: =PowerPivotMeasure[TotalRevenue]"
   
   Layout zones:
   - Top row: KPIs (Total Revenue, Total Orders, Avg Order Value, Top Customer)
   - Middle: Charts (Sales by Region, Sales Trend, Top Products)
   - Bottom: Data table with slicers

3. Create KPI cards using measures: - Cell A5: "Total Revenue" - Cell B5: =CALCULATE([TotalRevenue]) (from Power Pivot) - Format with currency, large font, colored background - Repeat for: Total Orders, Customer Count, Average Order Value
4. Add charts connected to data model: - Sales by Region (Column Chart): - Insert → PivotChart → Use Data Model - Axis: Customers[Region] - Values: [TotalRevenue] - Sales Trend (Line Chart): - Axis: Orders[OrderDate] (grouped by Month) - Values: [TotalRevenue] - Top 10 Products (Bar Chart): - Axis: Products[ProductName] - Values: [TotalRevenue] - Filter: Top 10 by value
5. Add slicers for interactivity: - Click any PivotChart → PivotChart Analyze → Insert Slicer - Select: Date (Year/Quarter), Region, Product Category - Position slicers on right side of dashboard - Connect slicers to all charts: Right-click slicer → Report Connections → Check all
6. Format dashboard professionally: - Remove gridlines: View → uncheck Gridlines - Use consistent colors (company brand colors) - Add borders around KPI cards - Align charts neatly - Add company logo in top-right
7. Set up automatic refresh: - Data tab → Queries & Connections - Right-click query → Properties - Check "Refresh data when opening the file" - Or add refresh button: Insert → Shapes → Button → Assign macro to refresh all
8. Test the dashboard: - Click slicers to filter - All charts and KPIs should update instantly - Click "Refresh All" to pull latest SQL data

💡 Tips:

• One-click refresh: Users get latest data without knowing SQL or Power Query
• Slicers drive interactivity: Users explore data by clicking, no formulas needed
• Publish to SharePoint/Power BI: Share dashboard with entire team
• Schedule refresh: Power BI Service can auto-refresh daily/hourly
• Performance tip: Import only needed columns and date ranges from SQL
• Version control: Save dashboard template, data refreshes but layout preserved

✅ Expected Result: Professional executive dashboard that automatically updates from SQL database with interactive filtering

🔄 Connection to Previous Learning: Culmination of all Excel skills (Days 1-23) combined with SQL data (Days 2-24)