Forum Discussion
Get Data from Excel: GhostSheet1, also defined Table vs raw data
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.
Besides, access is only for my prototyping. Excel from Splunk reports will be production.
Access: off-topic here
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)
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.
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 expectedI'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.