Power Query data source File.Contents

[suxilo53]

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]

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

	A	B
FilePath	FolderPath
C:\Users\Aconcagua\Google Drive\Excel\Forum\[FolderPath.xlsx]Hoja1	C:\Users\Aconcagua\Google Drive\Excel\Forum\

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD]=CELL( "filename", FilePath )[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD]=LEFT( FilePath, SEARCH( "[", FilePath ) - 1 )[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]FilePath[/TH]
[TD]=Hoja1!$A$2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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