Hi,

I want to know if there is some posibility to work with relative path (instead of Absolute path) in Power Pivot and Power Query with Excel 2010 and Excel 2013

Thanks in advance

• Changed type Michael AmadiModerator Friday, May 22, 2015 12:02 PM Thread is a question as opposed to a general discussion

Thank you for this return. So i think in any case we are not able to just set the name of field and Power Query goes to find the all path (relative path) ?

• Edited by micRey2015 Friday, May 22, 2015 2:02 PM

Well, i will try to be more clear. We develop our project on our PCs but once the project delivered to the client, folder path could change (But not the names of folders), so how we can handle this ?

• Edited by micRey2015 Friday, May 22, 2015 3:05 PM

Can you please tell me more dtails about the formula : =LEFT(CELL("filename"), FIND("[", CELL("filename"),1)-1) & "Data"

What should i put in "filename" ?

• Edited by micRey2015 Friday, May 22, 2015 4:42 PM

The reason why you're getting the error is because you need to change the name of the table to 'Directory'. You can do this by selecting a cell in the Directory table and clicking the Design tab in the Table Tools area that appears and then change the Table Name field to ' Directory'.

So from beginning to end you need to:

1) Create a single column table with 'Directory' as the header and use the following Excel formula to generate the relative path for the 'Data' folder:

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

Note: This formula will only return a value after the workbook has been saved to a location.

2) Use Format As Table option on the Home tab to turn the range into a table and change the Table Name property to 'Directory' via the Design tab (which appears after selecting a cell in the table).

3) In the same Workbook, create a new Blank Query via Power Query Tab > From Other Sources > Blank Query.

4) When the Query Editor pops up, click the View tab and then the Advanced Editor button before pasting in the following M code (remove the code that's already there) in the Advanced Editor window that appears:

let
    DirectoryTable = Excel.CurrentWorkbook(){[Name="Directory"]}[Content],
    DirectoryColumn = Table.Column(DirectoryTable , "Directory"),
    DirectoryFirstValue = List.First(DirectoryColumn),    
    Source = Folder.Files(Text.From(DirectoryFirstValue )),
    LoadBinaryAsTable = Table.AddColumn(Source, "Custom", each Csv.Document([Content]))
in
    LoadBinaryAsTable

5)  After pasting the code, click Done and you should see the list of Applied Steps in the Query Settings pane to the right.

6) Click on the expand icon in column called 'Custom' and pick the columns that you want to bring in and then click OK.

7) Do any required Power Query transformations.

You can download the zipped example here: https://onedrive.live.com/redir?resid=2c7894876b54db24!88866&authkey=!AOmP_ZxlN5BzsP4&ithint=file%2cz