CODEWITHSUNDEEP
excelpowerqueryexcel-2016m
Svish
Svish

Reputation: 158021

Relative path for Folder.Files

I have an Excel file, and in the same folder I have a folder with CSV files I want to include. Using the From Folder query thing, the first step will give the following query:

= Folder.Files("D:\OneDrive\Documents\Health\Concept2")

Now, this path will not be the same on my laptop for example, but the Excel file will always be in the Health directory...

I get an error if I try to just pass a relative path to Folder.Files, but is there a way to work around this? A fancy function? Some variable or thing I don't know about because I'm a total newb in this area?

Attempt 1

= Folder.Files(".\Concept2")
= Folder.Files("Concept2")

❌DataFormat.Error: The supplied folder path must be a valid absolute path.

Attempt 2

= Folder.Files(Activeworkbook.Path & "\Concept2")

❌ Expression.Error: The name 'Activeworkbook.Path' wasn't recognized. Make sure it's spelled correctly.

Upvotes: 4

Views: 7958

Answers (2)

Wedge
Wedge

Reputation: 1826

As far as I know you can't make a relative path reference entirely inside Power Query itself. What you CAN do however is setup a relative path reference inside your excel file, then load this into Power Query.

So setup a tab in your Excel file and make a one cell table with the formula 

=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"Concept2\"

To get your relative directory reference. Add this table into Power Query, and then you can get the relative directory for you file query like

Folder.Files(DirectoryQuery{0}[ColumnName])

Where "DirectoryQuery" is the name of the query created from your table and "ColumnName" is the name of the column in the table ({0} references the first row in the table).

Upvotes: 3

braX
braX

Reputation: 11755

= Folder.Files(Activeworkbook.Path & "\Concept2")

Upvotes: -2

Related Questions