First Steps: Connecting to a Data Source in Power BI (The Smart Way)

💡 Overview

At first glance, connecting to a data source in Power BI seems simple — just click New Source, right?

But it isn't.

Here are important things to keep in mind — not the sugar-coated stuff, but real scenarios that matter.

1. Fundamental

Case: There is only one Excel file with one sheet.

Easy: You just go, click on New Source, choose Excel, and select the sheet. Done.

2. Thinking Automation

Case: There is one Excel file with three sheets or tabs in it.

Noob: You go to New Source three times and connect each one.

Hidden Insight: If you ever need to transfer the file to a different PC or to a client, you'll have to go and manually fix Power Query three times. That’s just dumb.

Solution:

1. Connect to the Excel file once.

2. In Power Query, delete all steps except Source.

3. Rename it as Z Source.

4. Disable Load (Right-click → uncheck Enable Load).👉 Read more

5. Create 3 Reference queries for the three sheets.👉 Read: Understanding Reference vs Duplicate

Now you have every sheet independent on its own but tied to the main Z Source table. Think of how much potential time you have saved.

3. Folder Automation – Non-Standardized Files

Case: There are three different Excel files, non-standardized.

Noob: Connecting to each Excel file separately.

Solution:

1. Use Folder Connector.

2. In Power Query, keep only the Source step.

3. Rename it Z Source, and disable its load.

4. Now start creating Reference queries from this for each file’s logic.

Simple. Future-proof. Easy to maintain.

4. Folder Automation – Standardized Files

Case: There are 100 CSV files, but all have the same column structure.

This is a straight one — even if you are a noob, you would connect the whole folder. If you're thinking of connecting each Excel file, then you need to rethink this career. In your mind, you should already be on the journey toward AI/ML, not stuck in pen-and-paper workflows.

5. Folder Automation – Combined Standardized and Non-standardized

Case: It has both standardized and non-standardized files.

Think full automation here.

• Create a folder with all the standardized files.

• Create one folder with dimensions.

• Create subfolders if needed — e.g., if five files explain products, create a Product folder inside the Dimension folder, a Customer folder, and so on.

And everything will sit inside one master folder.

When referencing through the source, you just keep navigating through folder paths.

Simple — break down the folder path using the delimiter \

Create a group of just Z Source-based references.

All actual data queries should be references — not direct queries from Z Source.

Steps – For Product:

1. Create a query Z Source

2. Split the Folder Path column by delimiter \

3. Create a reference and call it Z Source - Dimension

4. Filter Folder Path 5 to Dimension

5. Create a reference of that and call it Z Source - Dimension - Product

6. Filter Folder Path 6 to Product

7. Disable Enable Load for all intermediate layers

8. Create the final Product table as a reference of Z Source - Dimension - Product

9. Name it Dimension - Product

Just think automation all the time.

6. Think Google Sheets vs Excel

If a client has no preference and is from a GSuite environment:

• Keep all their data in Google Sheets (only if the data is light and doesn’t have multiple standardized files).

• Publish the sheet to web.

• Use Web Connector in Power BI.

No login required. The best advantage — for scheduled refresh, no connector will be needed.

For Excel-based organizations:

• Use cloud files — via the client’s SharePoint.

Final Word

Just think automation always — make your future life easier.