• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Difference in code to import a sheet into Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Difference in code to import a sheet into Power Query|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryDifference in code to import a shee…
sp_PrintTopic sp_TopicIcon
Difference in code to import a sheet into Power Query
Avatar
Graham Stent
Member
Members
Level 0
Forum Posts: 17
Member Since:
November 3, 2015
sp_UserOfflineSmall Offline
1
May 7, 2021 - 7:50 pm
sp_Permalink sp_Print

Hi,

I'm struggling to understand what the difference is between the following 2 bits of code and was hoping someone could explain it for me. Both bits of code are to get data from a sheet in another Excel workbook. Code 1 is what I use 99% of the time as part of a standard template, but every now and then it fails and when I go through the PQ menu options to import the data it comes up with Code 2 ....... 

Code 1:
Source = Excel.Workbook(File.Contents(myFile), null, true),
#"ChooseSheet" = Source{[Item=mySheet,Kind="Sheet"]}[Data]

Code 2:
Source = Excel.Workbook(File.Contents(myFile), null, true),
#"ChooseSheet" = Source{[Name=mySheet]}[Data]

 

Thanks

Graham

Avatar
Graham Stent
Member
Members
Level 0
Forum Posts: 17
Member Since:
November 3, 2015
sp_UserOfflineSmall Offline
2
May 7, 2021 - 10:58 pm
sp_Permalink sp_Print sp_EditHistory

I had a thought on this ...... could it be that Code 1 is for importing from a closed workboook and Code 2 is for importing from an open workbook???

..... or could it be that Code 1 workbook does not have a password and Code 2 workbook does ...... does PQ treat them differently?

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
May 10, 2021 - 3:03 pm
sp_Permalink sp_Print

Hi Graham,

Code 1 is referencing a sheet name and code 2 is referencing a named range.

Mynda

Avatar
Graham Stent
Member
Members
Level 0
Forum Posts: 17
Member Since:
November 3, 2015
sp_UserOfflineSmall Offline
4
May 10, 2021 - 10:21 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mynda,

Thank you for the reply. Now you've enlightened me on that I can see it makes complete sense from reading the code 🙂

However, I think there is still a missing piece of the puzzle as I don't have a Named Range that I have defined.  The source workbook is password protected and has to be open for PQ to access the data in it. I think PQ must be dealing with password protected workbooks slightly differently to non-password protected workbooks. I need to go and do some more research on this. If I get to the bottom of it I will post back here.

I also need to look into why password protected files are much slower to retrieve data from as I think this is linked to my issue.

Thanks again ...... I love this site!

Graham

Avatar
Graham Stent
Member
Members
Level 0
Forum Posts: 17
Member Since:
November 3, 2015
sp_UserOfflineSmall Offline
5
May 10, 2021 - 11:36 pm
sp_Permalink sp_Print

Hi Mynda, 

So I've found out a little bit more. Password protected workbooks appear to be imported differently. If I look at the Power Query Editor at the first step ....  

      Source = Excel.Workbook(File.Contents(myFile), null, true)

..... then I get different results depending on if the workbook has a password or not. I have attached screenshots of both.

I guess this is just how it is designed to work??
It's incredibly slow to import the data ...... about 6 seconds for the non-password protected file .... and about 10 minutes for the password protected file!!

Do you know if there is anything different I can do when dealing with password protected files?

File-with-password.pngImage Enlarger

File-without-password.pngImage Enlarger

 

Thanks,

Graham

sp_PlupAttachments Attachments
  • sp_PlupImage File-with-password.png (43 KB)
  • sp_PlupImage File-without-password.png (269 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
May 12, 2021 - 9:48 am
sp_Permalink sp_Print

Hi Graham,

TBH I've never paid a lot of attention to the syntax of the Excel.Workbook function as this is something Power Query typically codes for you automatically. So, after some more research I think the difference between your two queries might be this:

Code 1 was built by Power Query automatically and Code 2 was built manually. In my testing both versions of the code return the same thing, so I suspect the reason one query is slow compared to the other is due to differences in the two files you're connecting to, rather than the code.

I doubt it has anything to do with the password protection. Your code is looking for data on a sheet that isn't in a table or named range. If these sheets have artifacts lingering in them (cells that used to have data/formatting in them that wasn't properly deleted), then this could be slowing down the query. Or maybe it's just a difference in the structure of the file that Power Query has to sift through to find what you want. 

I'd test for artifacts in the worksheet with the keyboard shortcut CTRL+END and if the cell you land in is not the end of the data, then that means there are cells in the file that appear, from Excel's point of view, to still contain data. You can try deleting all rows and columns outside the data range to see if that clears them, although in my experience this doesn't always help.

Mynda

Avatar
Graham Stent
Member
Members
Level 0
Forum Posts: 17
Member Since:
November 3, 2015
sp_UserOfflineSmall Offline
7
May 13, 2021 - 1:02 am
sp_Permalink sp_Print

Hi Mynda,

I did already test for artifacts in the worksheet using CTRL+END and that didn't show any anomalies.

Using Code 2 only:
My run time differences are based on the SAME file, once with it being password protected and once without. It really does seem to be that the slow speed is down to the file being being password protected!!

Thanks for your input on this.  I'll mark your original reply (post 3) as the answer to this thread as that gives the answer to the difference between Code 1 and Code 2 🙂

I'm still intrigued on the password bit though!

Thanks

Graham

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
May 13, 2021 - 9:44 am
sp_Permalink sp_Print

Hi Graham,

Thanks for clarifying that the tests were done on the same file. One thing to confirm, you cannot get data from an open Excel workbook, so that isn't a possibility for the different code or slow down in the file. It would appear it's down to the password protection. I'll do some testing at my end if I get time today and see if I get the same results.

Mynda

Avatar
Graham Stent
Member
Members
Level 0
Forum Posts: 17
Member Since:
November 3, 2015
sp_UserOfflineSmall Offline
9
May 14, 2021 - 12:05 am
sp_Permalink sp_Print

Hi Mynda,

I'm not sure if I'm misreading your comment above when you say "you cannot get data from an open Excel workbook". Just to confirm, I can get data from an open workbook and if a file is password protected then the workbook MUST be open for PQ to be able to access the data.......

Sorry if I've misunderstood what you meant ......

Any luck with testing?

Thanks
Graham 🙂

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
May 14, 2021 - 10:44 am
sp_Permalink sp_Print sp_EditHistory

Hi Graham,

You didn't misread my comment. There seems to be vastly different experiences for getting data from a workbook between your version of Excel and mine, assuming we're following exactly the same steps. I'm using Microsoft 365 and the following steps: Data tab > Get Data > From File > From Workbook.

If in workbook B I get data from an Excel Workbook (call it Workbook A), then workbook A must be closed, otherwise I get this error:

graham_workbook_open_error.pngImage Enlarger

 

If I put a password on Workbook A then close it and try the same, it then tells me the file is corrupt and won't open it at all. 

Mynda

sp_PlupAttachments Attachments
  • sp_PlupImage graham_workbook_open_error.png (23 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
May 14, 2021 - 1:42 pm
sp_Permalink sp_Print sp_EditHistory

Keep in mind that in normal pc folder you should see a temporary copy of the file you are opening, starting with "~$" then your file name. You cannot see this file in the folder if your system file setting is hiding these, but they are there. (unhide if you want to see these)

It's the temporary file that PQ tries to access and says that the file is being used. This means that Excel is NOT really opening the file you wanted, it's opening a hidden COPY instead. If you don't save changes, your original file stays untouched. It will be overwritten by the temp copy IF you save changes. During file editing, the original file is CLOSED.

I was able to successfully connect using PQ to an open file by using File from folder instead of From Workbook, Filtering OUT file names that starts with ~

then filter to select only the single file I need.

Care: if you make changes to the temp copy, the changes will NOT be seen by PQ until you SAVE the changes.

 

For not normal folders, and I mean here OneDrive and sharepoint folders, Excel behaves differently, since few months ago I am no longer seeing the temp copies as in normal folders.

See image attached, if you filter out the temp file, you will be able to access the closed original version, even if the file is "open" (in microsoft style)...

1-3.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage 1-3.jpg (54 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
May 15, 2021 - 1:46 pm
sp_Permalink sp_Print sp_EditHistory

That's interesting, Catalin. However, I still get errors when trying to get data from folder where one or more of the files are open. ‍

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
13
May 15, 2021 - 2:32 pm
sp_Permalink sp_Print

What errors?
I tested again, and the trick still works, I can refresh a query from folder even if files are open.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
14
May 15, 2021 - 6:10 pm
sp_Permalink sp_Print

In a folder I have 3 files, one of which is open. I 'get files from folder' in a new workbook and when I click the double down arrow on the Content column containing the Binaries nothing happens. If I add a custom column with =Excel.Workbook([Content]) I get the following error for the workbook that's open:

"DataSource.Error: The process cannot access the file 'C:\Users\mynda\pq_2.05_dec.xlsx' because it is being used by another process.
Details: C:\Users\mynda\pq_2.05_dec.xlsx"

Same happens with your sample files.

Mynda

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
May 15, 2021 - 7:45 pm
sp_Permalink sp_Print

Ok, so I assume you have filtered out the files starting with "~" before extracting data from those files?

= Table.SelectRows(Source, each not Text.StartsWith([Name], "~"))

Strange, I can refresh the query with the files I sent open.

Have you run the query from the file "get data from open books.xlsx" included in archive? That query works with open files. (you just have to change the folder path to your computer)

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
16
May 17, 2021 - 9:07 am
sp_Permalink sp_Print

Hi Catalin,

I downloaded your archive and changed the file path to point to my PC and I get the error I mentioned above.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard West, Clayton Watson
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27292

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.