3

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.

Svish
  • 152,914
  • 173
  • 462
  • 620

2 Answers2

3

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

Wedge
  • 1,766
  • 1
  • 8
  • 14
  • Also see https://techcommunity.microsoft.com/t5/Excel/Power-Query-Source-from-Relative-Paths/td-p/206150 – FH-Inway Feb 25 '19 at 17:49
-2
= Folder.Files(Activeworkbook.Path & "\Concept2")
braX
  • 11,506
  • 5
  • 20
  • 33
  • *Expression.Error: The name 'Activeworkbook.Path' wasn't recognized. Make sure it's spelled correctly.* – Svish Jun 08 '17 at 18:58
  • oh - I thought you were using VBA within Excel. I'm not familiar with PowerQuery. Sorry. – braX Jun 08 '17 at 19:09