Power Query data source File.Contents

suxilo53

New Member
Joined
Jan 16, 2017
Messages
9
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

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

Board Regular
Joined
Dec 27, 2016
Messages
178
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
AB
1FilePathFolderPath
2C:\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
CellFormula
A2=CELL( "filename", FilePath )
B2=LEFT( FilePath, SEARCH( "[", FilePath ) - 1 )

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
FilePath=Hoja1!$A$2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

The code for the FolderPath query is the following:
Code:
// FolderPath
let
    Source = Excel.CurrentWorkbook(){ [Name = "FolderPath"] }[Content], 
    FolderPath = Source{ 0 }[Column1]
in
    FolderPath
I hope it helps.
 

suxilo53

New Member
Joined
Jan 16, 2017
Messages
9
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... :)
 

Forum statistics

Threads
1,084,933
Messages
5,380,652
Members
401,695
Latest member
dwoychowski

Some videos you may like

This Week's Hot Topics

Top