Case Study: Connecting Multiple Sheets from One Excel File in Power BI (Clean & Scalable)

nitin rungta
Apr 10
2 min read

Updated: Apr 21

Overview

You’ve received an Excel file with three sheets. Simple project. But if you connect all three manually, you’re setting yourself up for pain later.

Most noobs click 'Get Data' three times and connect each sheet separately. That works—until the file moves, or you need to refactor, or build something bigger.

Here’s the cleaner, smarter, and more scalable way to connect multiple sheets from the same Excel file.

Note - All blogs related to Excel also apply for Text and CSV files. Only different is you select Text/CSV instead of Excel in Get Data

Case: One Excel File, Three Sheets

File: SalesMaster.xlsx

Sheets: Sales, Products, Customer

What Most People Do (Noob Method):

- Click Get Data → Excel

- Select 'Sales' → Load

- Repeat the same steps for 'Products' and 'Customer' Or they would selct the sheets altogther while loading the first time (which is also not recommended)

Problem: Now you have three different source connections. If the file path changes or you need to send this to someone, you’ll need to manually fix all three queries. Waste of time.

The Smart Way (Recommended):

Step 1: Click Get Data → Excel → Select the file → Click Transform Data

Notice in the above step, all Sheets or Tabs in the Single Excel file have displayed. Choose any one at Random and click on Transform Data.

- Step 2: In Power Query, delete all steps except the first one (Source)

- Step 3: Rename the query to Z Source

- Step 4: Disable Load (right-click Z Source → uncheck Enable Load)

Notice above how the Text Changed to Italics when we Disable the Load.

- Step 5: Right-click Z Source → Reference → rename to Fact - Sales

- Step 6: Right-click Z Source again → Reference → rename to Dim-Products

- Step 7: Again, Reference → rename to Dim - Customers

- Step 8: In each loaded table/query, filter to the relevant sheet and apply cleaning steps.

Start by selecting Table to get into the query. Please understand each step that happens in your Query, because lets face it, irrespective of the outcome, You are RESPONSIBLE for everything.