Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BarryB
Frequent Visitor

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

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")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

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

7 REPLIES 7
BarryB
Frequent Visitor

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
Community Champion
Community Champion

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)



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

BarryB
Frequent Visitor

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.

 

import Excel.png

 

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
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

BarryB
Frequent Visitor

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
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

v-xjiin-msft
Solution Sage
Solution Sage

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.