tmacg7
New member
- Joined
- May 2, 2016
- Messages
- 9
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365 / V1803
Getting really frustrated with myself for not being able to figure this out...
I have a PowerQuery to the Current Workbook (querying the same workbook the query is set up in) and I want to be able to move the file to a different folder or rename the file without the query breaking. There has got to be a simple way to do this... I'm new to PowerQuery and don't really know what I'm doing or how to use the advanced editor. I'm reading all Ken's posts about parameters tables, and I just got the Data Monkey book and am skipping to chapter 23 about dynamic parameters tables, but I don't get it (I know I should read the whole book first, but I just need to figure this one thing out as soon as possible). All the examples I'm seeing are trying to do more than I want to do -- I don't need to load "all the files from a folder" -- I just need to query a worksheet in the same workbook and not have it break when I move or rename the file.
I can see that if I choose to query a proper Excel table within the current workbook (by choosing "From Table"), I can then rename the file or move it to a different folder and the query automatically updates and works. But if I'm querying a whole worksheet (not table format) in the current workbook, I have to use the "From File" method instead of the "From Table" method, and I have to navigate to the file itself to set up the file path. Then if I change the file name or move the file, the query breaks and says it can't fine the file.
I see that when I go to the advanced editor and look at the code for a query that was set up "From Table", it has a "Excel.CurrentWorkbook" in the source. But when I look at the source for a "From File" query, it has the actual file path:
So isn't there a way to replace the file path wording with the "CurrentWorkbook" method? Or something that works like that?
I'm using Excel 2013, if that's important.
Thank you!
I have a PowerQuery to the Current Workbook (querying the same workbook the query is set up in) and I want to be able to move the file to a different folder or rename the file without the query breaking. There has got to be a simple way to do this... I'm new to PowerQuery and don't really know what I'm doing or how to use the advanced editor. I'm reading all Ken's posts about parameters tables, and I just got the Data Monkey book and am skipping to chapter 23 about dynamic parameters tables, but I don't get it (I know I should read the whole book first, but I just need to figure this one thing out as soon as possible). All the examples I'm seeing are trying to do more than I want to do -- I don't need to load "all the files from a folder" -- I just need to query a worksheet in the same workbook and not have it break when I move or rename the file.
I can see that if I choose to query a proper Excel table within the current workbook (by choosing "From Table"), I can then rename the file or move it to a different folder and the query automatically updates and works. But if I'm querying a whole worksheet (not table format) in the current workbook, I have to use the "From File" method instead of the "From Table" method, and I have to navigate to the file itself to set up the file path. Then if I change the file name or move the file, the query breaks and says it can't fine the file.
I see that when I go to the advanced editor and look at the code for a query that was set up "From Table", it has a "Excel.CurrentWorkbook" in the source. But when I look at the source for a "From File" query, it has the actual file path:
From Table:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
From File:
Source = Excel.Workbook(File.Contents("C:\Users\Smith\Desktop\Folder 2\File2a.xlsx"), null, true),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
From File:
Source = Excel.Workbook(File.Contents("C:\Users\Smith\Desktop\Folder 2\File2a.xlsx"), null, true),
So isn't there a way to replace the file path wording with the "CurrentWorkbook" method? Or something that works like that?
I'm using Excel 2013, if that's important.
Thank you!
