Get Data from Excel: GhostSheet1, also defined Table vs raw data

[BarryB]

Sorry to conflate two enquiries but I'm trying to better understand the "Get Data" import process with Excel 2013.

I'm importing an Excel worksheet, itself the result of an exported query from MSAccess 2013 (no direct connection: 32/64 bit incompatabilities... off topic here)

This screenshot shows

1) the worksheet, a "ghost" worksheet (with a "1" appended)...

2)... and the same data selected as a table (CTRL + "T") within the worksheet ("Table1")

original spreadsheet, "ghost spreadsheet, and spreadsheet saved as a table (using"CTRL + T")

for (1), where did this "ghost" Worksheet1 come from and what do the two different symbols between "Worksheet" and "Worksheet1" actually mean? On preview, they both seem to display the same data, and either of them seem to work for the UI.

for (2), is there any value in defining a table within the worksheet beforehand? I ask this specifically because I'm trying to keep the steps as simple as possible for a team of accountants when dealing with tens of thousands of rows and dozens of columns. If this has no value for PowerBI, I can just ignore this step.

I've been looking at multipe examples of importing Excel, and not found instances of ghost worksheets, nor of people bothering to define a table within the worksheet.

Many thanks for your help.

BarryB

[BarryB]

As mentioned in the initial post, 32/64 bit incompatabilities.

Besides, access is only for my prototyping. Excel from Splunk reports will be production.

Access: off-topic here

[Stachu]

I see 3 different objects in Excel in your screenshot

1) Table

2) Sheet

3) Named range

They have 3 different icons for a reason, if you add more sheets more icons like 2) would appear
so my guess is that the "ghost" sheet is just a named range (probably created when importing from Access)

[BarryB]

Thanks for that - it's starting to make a bit more sense.

1) Table - yes, that's a given from creating the Table within the Spreadsheet via CTRL+T

2) & 3):

I'm not so sure about the named range, per se, because the Excel is simply an intermediate step from Access - the export of the results from an SQL query.

"if you add more sheets"

Which I did (forget the Table - blew it away as a confusing the issue).

Exported another query from Access to another spreadsheet, then added it to the initial spreadsheet turning it into a multi-sheet workbook.

I suppose the bigger question is which one to use for PowerBI, and does it make any difference? They both seem to get access to the same data.

[Stachu]

I would probably use a sheet as I don't know the range updates properly when data changes
the risk is if someones edits the file it will also extract data from additional columns etc., which may not be as expected

[BarryB]

I'm not deliberately using named ranges in the Access export - in fact the view of the spreadsheet in Excel is no more fancy than viewing a CSV file.

I suspect there's a knowledge gap here for PowerBI users generally, if it's coming down to guessing.

I take it by "worksheet" you mean the file/icon without the number appended on the end?

I'm still unclear on what the two symbols actually mean per worksheet, and why the second one is there at all.

[Stachu · Accepted solution]

The thing is your question doesn't really concern PowerBI:

When you export from Access to Excel it creates 2 things in Excel:

1) worksheet named after query/table you export

2) named ranged with only the used cells within that worksheet
I would use worksheet rather than range cause I don't know your process - when the data is updated you meay create a new file, update existing one, etc. - none of that is related to how Power BI handles it,

The guess is regarding your process, rather than what PowerBI will do - if you connect to worksheet it will extract all the cells with data, if you connect to range, it will only extract that range, it's very simple.

[v-xjiin-msft]

Hi @BarryB,

Since the data in your excel file was from Access database. Why don't you just connect to Access database directly in Power BI?

Thanks,
Xi Jin.