Excel Power Query with file being used

[taylorsm]

Is there a way to still use Power Query even if the source file is open?

[sandy666]

You can use PQ with open source file, closed source file, etc.
Question is not clear.

[taylorsm]

No it's pretty clear, it's just a simple question, sorry. Can you use it if the file you are pulling data from is open.

I have a PQ but if I have one of the files open then it gives me an error and does not update. As soon as I close the file, the PQ updates fine.

[sandy666]

Check PQ rights, check file rights, check access server rights .....
Like I said, question was not clear.
But If you know better, good luck.

[taylorsm]

I don't know better that is why I am on this site. There was no indication that I was saying you were wrong on an answer, you didn't even offer an answer for me to "know better". But i really don't know how else to word it, i'm sorry. PQ pulls data from several workbooks. if one of those workbooks are open then it fails. "Process cannot access the file because it is being used by another process." Hence the question if you could do a PQ if the source workbook was open.

[sandy666]

If you have access to the files from the same place (I assume that) and only one file gives you that warning so you can...
kill all excel proccesses and try again (this file can be opened in the background)

[taylorsm]

This specific scenario.

I have 8 files in a second tier folder. My PQ file is in the parent folder. I direct my PQ to that subfolder. I have tried it with all of the eight files. Same result. I created these files. There are no extra restrictions placed on the files.

[sandy666]

And seven files works well but one (the same) not?

[taylorsm]

It stops the process. I have no clue where it does though or how much gets updated. It just gives me that error, names the specific file and under Queries and Connections it says Download did not complete.

[sandy666]

• Did you kill excel proccess? (the best way is restart PC)
• Did you try to recreate connection to this file?
• All files are independent from each other (no connection between them)?
• Did you try to create brand new source file and connect it via PQ?

I assume the warning is for the same file all the time

[taylorsm]

1. Yes
2. No
3. Yes
4. No

[taylorsm]

Instead of just giving me the name of the open file, it is doing ~$Name, so its like the temp file

[sandy666]

Maybe you tryin' to load the same file twice.
Sometimes PQ needs more time (PQ problem) to load especially if data from source is big and some transformation is there. But I suppose you tryin' to get table from source only without any transformation (based on your previous thread)

edit: I tried to recreate your problem (with 5 files - I'm lazy ) and all works well. Doesn't matter files are closed , open, partially closed/open.

You can try to clean Temp folders (windows and user) and try again

[taylorsm]

Yeah. pretty straightforward. 14 columns. 200 or so rows. basic data entry.

I'll clean it all up and try again now that I know PQ should work. If I can get it to work, then I'll be a made man. At least for that worksheet...God I hate shared files.

[sandy666]

Are you tryin' connect files via Query from file OR Query from folder?

[taylorsm]

Get Date > From File > From Folder

That is the path I followed

[sandy666]

It was suggestion If one way doesn't work - try another

Eight files is not too much

[taylorsm]

I'm sure I built it wrong since it is my first time ever building a PQ and soruces online/youtube are not very helpful on how to pull data from a workbook that has other unwanted worksheets. Online is all, compile worksheets in same workbook or compile workbooks.

[sandy666]

So try with Query from file

[taylorsm]

OK. but how do I do that, then combine the 8 sheets?

[sandy666]

Eight sheets or eight workbooks?
Could you explain structure of the single file?

[taylorsm]

Sorry. Eight workbooks. Each workbook has about 4 sheets, but only one of those is being pulled.

The sheet is just 16 columns with headers that may go 200 rows deep

[sandy666]

Ok,
call (load) first workbook, select this table what you want, load as connection, now you can rename name of the table to eg. Client1
do the same for all other workbooks.
Next you can load selected Client(n) to the choosen sheet with apprpriate name.. Eg. Table Client1 to the sheet called Client1 (or table John to the sheet John).
Before you strat try to prepare main workbook with all sheets, John, Mark, Mary, etc...

Columns and rows doesn't matter because you will work with tables first

[taylorsm]

So is this making my workbook have 8 sheets as well? Instead of just one merging their 8?

[sandy666]

You said you want one table from the source
Maybe try to describe what you want as result

first workbook (source), few sheets but one is needed.
second workbook (source), few sheets but one is needed.
etc...

result?

[taylorsm]

Workbook one,two,three,four each have all these sheets "Prod Totals", "Tracker", "Teams", "DCMR."

I want to pull "DCMR." from all four and merge them into one master "DCMR." sheet.

[sandy666]

So load DCMR tables from each workbook as connection then merge them and then load merged table to the sheet as table

(it's hard to explain in text. )

Merge queries

[taylorsm]

So copy the eight separate sheets into mine and then merge them?

[sandy666]

You've 8 workbooks
Each workbook contain DCMR (sheet/table)
Load DCMR from the first workbook as connection
Load DCMR from the second workbook as connection
etc...
Merge all DCMR queries into one
Load merged DCMR as table into the appropriate sheet in your main file

[taylorsm]

OK. I'll give that a shot. thanks

[sandy666]

btw. If I don't see structure and kind of data I can't say merge or append it will be your choice
maybe load each DCMR as table one after another will be enough because clients are different

[taylorsm]

https://social.technet.microsoft.com...rum=powerquery

here is someone else having the same issue. No clue what the 'answer' means.

[taylorsm]

I think I got it. I figured out how to filter out $

but another question. The PQ is copying the headers over from each sheet so that I have 8 instances of headers in my table. How do I not have that?

[sandy666]

But now (Query from file) you don't have ~$ (I hope)

[sandy666]

The PQ is copying the headers over from each sheet so that I have 8 instances of headers in my table. How do I not have that?

I don't know what you did.
Try to describe your action everytime if you've question about result of your action. With details
Read my blue footnote

here is a good start for you: Import data from external data sources (Power Query)