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

 

Hello learners,

In this tutorial, I am going to explain how to connect your Excel file into Power BI Desktop. In the previous videos, I have explained how to install Power BI Desktop on your Windows operating system. If you have any issues with that, you can watch that video. From this video tutorial, I will show you how to get your Excel data into Power BI Desktop. I have already opened Power BI Desktop. As I mentioned before, this is a free tool provided by Microsoft to develop dashboards and reports.

Method to Import Excel

1. On the canvas, click the "Import data from Excel" button.
2. In the Home menu, go to the Data tab and click Excel workbook.
3. Click the "Get Data" dropdown, choose Excel workbook, or select More to open another window.
4. In that window, under File, select Excel Workbook, and click Connect.

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!

Read More

How to Highlight Specific Segments in Power BI Line Charts

Ever wanted to highlight just one part of a line chart in PowerBI? Maybe to emphasize a trend, a peak, or a dip? Well, now you can with the new line segment formatting feature.

Hey everyone, welcome back to my blog Data 365 Snippets. In this post, I'll walk you through an exciting new feature in PowerBI, the ability to customize line segments with individual line colors and transparency. It's a gamecher for storytelling in your visuals.

Getting Started with Line Segment Formatting

Let's jump right into PowerBI and see how it works. I've created a simple line chart here showing sales over time. 

Let's select the visual and go to the format pane.

You'll now notice something new inside the lines and shared area cards, a drop-down labeled apply settings to. 

Click the drop-own and select a specific data point.

Customizing Line Segments

This is where the magic happens. You can now change the line color, transparency, and even the shading for just that segment. Perfect for calling attention to important trends.

Finally you will get yout line chart like this,

This builds on the already available support for customizing individual markers. Now we can control the entire segment between markers.

Real Use Case: Black Friday Spike

Let's say on November 24, we saw a massive jump in sales due to the Black Friday campaign. We can now highlight just that segment in any color of your choice to make it stand out.

Bonus Tip: Add Detail Labels

And here is a bonus tip. You can add a detail label to provide more insights into why there's a huge dip or increase in sales during that period of time.

Coming Soon: Conditional Formatting

The only feature that is missing now is to be able to conditionally format these lines. And the conditional formatting feature is going to be released very soon. So, I'm sure we all are looking forward for it.

Final Thoughts

So, what do you think about this new update? Drop your thoughts in the comments. If you found this video helpful, give it a thumbs up and don't forget to share for more PowerBI tips every week.

Read More