Duplicated data with underscores when loading excel file in Power BI Desktop - Which one to choose

[Anonymous]

Hi there, I am using Power BI Desktop to create a dashboard. When I load the data from an excel file, there are duplications with the difference of adding an underscore for my sheets/data ranges in the excel. For example, there are "ADS Expertise", "ADS ExpertiseLevel", "ADS StaffExpertise", and "AISBAS Staff" in my excel. But when I loaded the excel, there were "ADS_Expertise", "ADS_ExpertiseLevel", "ADS_StaffExpertise", and "AISBAS_Staff" additionally, as shown in the image below. May I know which one (underscored or non-underscored) should I use to create the dashboard? What are the differences between the original and underscored ones? Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous 

Just as rajendraongole1 post that the underlined part is the standard table that exists in your .xlsx file, and the part without the underscore is the part of the table that Power BI automatically recognizes, which may not be very accurate sometimes.

Here are some ways of creating tables in Excel as a supplement:

Create a table in Excel - Microsoft Support

7 Ways to Make a Table in Microsoft Excel | How To Excel

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous 

Just as rajendraongole1 post that the underlined part is the standard table that exists in your .xlsx file, and the part without the underscore is the part of the table that Power BI automatically recognizes, which may not be very accurate sometimes.

Here are some ways of creating tables in Excel as a supplement:

Create a table in Excel - Microsoft Support

7 Ways to Make a Table in Microsoft Excel | How To Excel

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for your answers @rajendraongole1 @Anonymous! But I am a bit confused. @Anonymous said the part with the underscore is the original data in the .xlsx file as @rajendraongole1 posted. But I think what @rajendraongole1 posted said that the non-underscored part is the original data? Did I get you wrong here?

[Anonymous]

Hi @Anonymous 

What I mean by this is that the underlined part is the data in the form of a table (you've formatted it as table in Excel), and the one that doesn't have an underscore is that your Power BI automatically divides the data in the .xlsx file that might be a table, which is automatically generated, so the non-underscored part is the original data.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Anonymous . It means that if there are two versions for the same table, it's better to use the non-underscored one as the underscored one by Power BI itself sometimes may not be very accurately. Is my understanding correct?

[Anonymous · Accepted solution]

Hi @Anonymous 

No, this has nothing to do with the accuracy of the data itself, it only affects the scope of Power BI recognition.

Here's a sample for your reference:

I have 2 tables in the .xlsx file:

when I didn't format them as tables, the power bi could recognize the two tables as one:

But we can select the data and format it as table separately:

Then Power bi can recognize correctly:

Misidentification is still preserved:

Whether it is underlined or not, you can use it as long as the range of cells it selects is correct.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Got it. Thanks for the detailed explanation!

[rajendraongole1]

Hi @Anonymous -In your Excel workbook contains properly structured tables or named ranges, the non-underscored versions should reflect your original data better.

These versions could be duplicates, or in some cases, reflect ranges or data that Power BI is interpreting from your workbook. You might want to compare the two and check if both are pulling in the same data or if there’s any difference in structure.

To avoid confusion, it's a good idea to maintain a clear structure in your Excel file, using tables or named ranges with consistent naming practices. This way, Power BI can accurately map your data without needing to auto-generate additional versions with underscores.

Hope this information helps.