Power Query Relative Path

Question

Hello,

I have been working on a power query for about two months. The query uses an absolute path and I am wondering if there is an easy way to create a relative path.

1. The file will always have this name: ckoperations
2. The file will always be in a folder named: CK Inventory
3. This folder will always be located on the desktop
4. The computer used will not always be the same

I'm wondering if there is some kind of source code that can use a relative path similar to the following:

..\desktop\CK Inventory\ckoperations.xlsx

I do not understand the programming language well, so if you happen to have a solution I could copy and paste that would be appreciated.

Thank you,

Chelsea

EDIT: I was able to figure out how to set a relative path using the selected answer, but I did have to make some adjustments when following the instructions. I added those adjustments in a reply below.

Answer 1

Dear Chels,

Based on the requirements, you may first connect to the workbook by Power Query and use the formula like =LEFT(CELL(“filename”,$A$1),FIND(“[“,CELL(“filename”,$A$1),1)-1) to create a cell for Power Query to catch the file path.

As you want the file to be accessed by multiple computers, you may need to create the shared network path instead of the local path on one computer.

For the detailed steps, please refer to the article.

***Disclaimer:***Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

If something is misunderstood, welcome to post back with your updates at your convenience.

Thanks for your effort and time.

Cliff

Answer 2

Dear Chelsea,

Thanks for your updates and sharing the further information with us.

I'm glad that you have been able to create the relative path using this method.

As the path may become the online path when you upload the file to the OneDrive for Business site, some issues may happen. I suggest you first save the file to the OneDrive for Business site, sync the file with the OneDrive sync client and use the file path in the local sync folder as the relative path to see if it works in your environment.

Thanks for your effort and time.

Cliff

Answer 3

you may first connect to the workbook by Power Query and use the formula like =LEFT(CELL(“filename”,$A$1),FIND(“[“,CELL(“filename”,$A$1),1)-1) to create a cell for Power Query to catch the file path.

Hi Cliff,

Thank you for the link. I was able to create the relative path using this method. The following information is for anyone that may run into the same issues I did.

I kept getting an error using that formula so I split it into three steps and named cell A3 "filepath." Column I shows what is written in Column A.

Then I followed the instructions from the link, which added one line (line 2) to my code and made some adjustments to the Source line.

My old code did not contain line 2 and the source line looked like the following:

Source = Excel.Workbook(File.Contents("C:\Users\***\Desktop\CK Inventory\ckoperations.xlsx"), null, true),

One problem I encountered was missing a comma at the end of line 2.

Also, this solution did not work when I had the file saved to OneDrive. It had to be on the physical computer.

All the best,

Chelsea

Answer 4

Hi Chelsea

The following videos will help you

Regards

Jeovany

https://www.youtube.com/watch?v=0NX-GctfZuU&t=520s

https://www.youtube.com/watch?v=ppM-mLckQfs