Day 2: Connecting to Data Sources

Home → Get Data → Excel Workbook → browse to the file → Navigator window shows all sheets and tables → select the ones you need → click Load (imports immediately) or Transform Data (opens Power Query Editor for cleaning first). Best practice: always convert your Excel data to a Table (Ctrl+T in Excel) before connecting — Power BI recognises named Tables and loads them cleanly, whereas sheet ranges can include blank rows and headers incorrectly.

Home → Get Data → SQL Server → enter Server name and Database name (optional) → choose Import or DirectQuery mode → enter credentials (Windows or SQL Server authentication) → Navigator shows available tables and views → select tables → Transform Data or Load. For large production databases: connect using DirectQuery to avoid copying millions of rows into the model. For reporting databases or data warehouses: Import is faster for report performance.

Power Query is the data transformation engine in Power BI (same engine as in Excel). It handles the Extract, Transform, Load (ETL) process — connecting to sources, cleaning and shaping data, and loading it into the data model. Every transformation is recorded as a step in Applied Steps and can be refreshed automatically. Power Query is where you fix data quality issues before they enter your data model — missing values, wrong data types, inconsistent text, unnecessary columns. Clean data in Power Query means clean visuals in reports.

Home → Get Data → More → Folder → select the folder path → Combine & Transform Data. Power BI reads all files in the folder, creates a sample file query, a transform function, and a combined query that applies the same transformations to every file and stacks the results. This is the standard approach for combining monthly sales files. Every month, drop the new file in the folder → click Refresh → the combined dataset updates automatically. All files must have the same column structure.

Load imports data directly into the Power BI model without any transformation — whatever comes from the source goes straight in. Use Load only when the data is already clean and correctly typed. Transform Data opens the Power Query Editor, where you can preview the data and apply transformations before loading. Always use Transform Data on first connection so you can verify column names, data types, and spot obvious issues. You can always load without transforming later once you know the data is clean.