How to Connect and Clean Excel Data in Power BI Desktop: A Beginner’s Guide

Selecting the File

Now, it will open the file explorer. Go to Documents, open the Power BI folder, and select your Excel file (e.g., "Sample data for dashboards and report development"). Click Open. It will take 5 to 10 seconds to load the data into Power BI.

Navigator Window

In the Navigator window, you will see two checkboxes. These represent the two sheets in the Excel file. 

Let’s open the Excel file — you can see the sheets: Sales Data, Sheet1. Since I want to load only one sheets that is Sales Data sheet, I will select all of them and click the Load button.

Transform Option

If you want to transform your data before loading it, click the Transform Data button. For this tutorial, I will directly load the data by clicking Load Data.

Data Loaded

You can now see the sheet named Sales Data, Returns — under the Fields pane. Under Sales Data sheet, the available column names are listed. If you open the Excel file, the column headers match what you see in Power BI.

[Data Type Symbols]

Next to each field, you might see symbols:

1. Sigma (∑) for numeric fields
2. Calendar icon for date fields
3. Blank icon for text fields
4. These help identify data types quickly.

Header Row Issue

In the People and Returns sheets, you might see columns named Column1, Column2, etc. — this is due to Power BI not recognizing the first row as headers.

This is a common issue when loading Excel files. I purposely created this to show you how to fix it.

Using Power Query Editor

To fix it:

1. Click Transform Data at the bottom of the screen.
2. This opens Power Query Editor.
3. You will see sales Data sheet.
4. For the Sales Data sheet:
5. Click Use First Row as Headers.

You’ll notice a step added under Applied Steps: "Promoted Headers" and "Changed Type".

You can undo this by removing those steps if needed.

Go to the Transform menu and select Use First Row as Headers.

It will rename columns correctly.

Finalizing Changes

Now go to the Home menu and click Close & Apply.

This will refresh the data and display correct column names under People and Returns sheets.

Conclusion

This is how you can connect your Excel file with Power BI Desktop. In the next post, I’ll show you how to connect other data sources like csv , sql server, web in Power BI Desktop.

Thank you!