# Power Query data source File.Contents



## suxilo53 (Feb 21, 2017)

Dears,

I have a very stupid question. I tried to find a solution using search function but I didn't find anything

My question is:
How can I modify this code 


```
let    Source = Excel.Workbook(File.Contents("C:\Users\ymitio\Desktop\...\my source file.xlsm"), null, true),
    T1_Sheet = Source{[Item="T1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(T1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type any}, {"Column20", type text}, {"Column21", type any}, {"Column22", type any}, {"Column23", type text}, {"Column24", type any}, {"Column25", type any}, {"Column26", type text}, {"Column27", type any}, {"Column28", type any}, {"Column29", type text}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type text}, {"Column41", type text}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, 

....


    #"Filtered Rows"
```

considering that I have 2 files. My source file.xlsm where I have data and, in the same directory, an xls file where I set-up the query. I would like to configure the query to use a relative link and not all the path to the file.

Thanks a lot in advance for support...


----------



## FranzV (Feb 21, 2017)

You can use the info_type "filename" of the CELL() function to get the name of your destination file along with the complete folder path where it is stored.  You can then use LEFT() combined with SEARCH() to keep only the folder path.  

If you use the formula inside a named range or a table, just select the cell and choose 'From Table' in the Power Query group of the Data tab in the menu ribbon.  You will have a new query with the folder path.  You can just right click on the value and choose 'Drill Down' to access the text value.  

This way you could use _FolderPath & "my source file.xlsm"_ as a dynamic reference to your source file (as long as it is in the same folder).




*Excel 2016 (Windows) 64 bit*
AB1FilePathFolderPath2C:\Users\Aconcagua\Google Drive\Excel\Forum\[FolderPath.xlsx]Hoja1C:\Users\Aconcagua\Google Drive\Excel\Forum\

<colgroup><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>

*Worksheet Formulas*
CellFormulaA2=CELL( "filename", FilePath )B2=LEFT( FilePath, SEARCH( "[", FilePath ) - 1 )

<thead>

</thead><tbody>

</tbody>

<tbody>

</tbody>

*Workbook Defined Names*
NameRefers ToFilePath=Hoja1!$A$2

<thead>

</thead><tbody>

</tbody>

<tbody>

</tbody>
The code for the FolderPath query is the following:

```
// FolderPath
let
    Source = Excel.CurrentWorkbook(){ [Name = "FolderPath"] }[Content], 
    FolderPath = Source{ 0 }[Column1]
in
    FolderPath
```

I hope it helps.


----------



## suxilo53 (Feb 21, 2017)

Dear FranzV,

thanks a lot for your reply.
It's quite complicated for me your suggestion but I will try to apply it to my project...
I have to study a little bit more...


----------

