Relative Folder Path: Power Query Guide

[@abbadq]

👍 key point to knowledge.

[@howtolearnexcel]

Thank you!

[@FsoOmar]

Great Tip! Thank you.

[@howtolearnexcel]

Yeah, saves some time. I wish it were added as an native option in PQ. Thank you!

[@omarnader776]

Great

[@howtolearnexcel]

Thank you! So glad it's helpful!

[@constantsignals4474]

works perfect for local files/folders. but does not work for me when moving the folder to onedrive/sharepoint. any suggestions?

[@howtolearnexcel]

Interesting question. I need to research it a bit. By the way, do you mean a personal OneDrive or OneDrive for Business? As for SharePoint, you should check this video: kzbin.info/www/bejne/o5i5n55qh5xsl8U P.S. I'll try to find the solution and answer a bit later.

[@constantsignals4474]

@@howtolearnexcel thanks for linking the sharepoint video. I was talking bout OneDrive for business. In our company it often happens that people do some analysis local and then want to share it with onedrive or sharepoint. the static paths always break

[@howtolearnexcel]

I can confirm that this is possible. But it is a bit tricky. For example, to connect to your own Business OneDrive directory, you need to get this part of the address: comapanyname-my.sharepoint.com/personal/login/ (and delete everything else). If your consolidation file is placed there too, you can use the formula to grab the part before the 5th slash. Like this: =LEFT(CELL("filename",A1), FIND("#", SUBSTITUTE(CELL("filename",A1), "/","#", 5))) Then place it in the "Table1" - "Path" column. The PQ code will be following: let Source = SharePoint.Contents(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[Path], [ApiVersion = 15]), Documents = Джерело{[Name="Documents"]}[Content]{[Name="SubfolderName"]}[Content] in Documents The first line grabs the relative path. The second is where you define the subfolder (as many levels as needed). You can use SharePoint.Contents or SharePoint.Files function depending on what you need. If you want, you can modify your code to work with local files and the SP\OneDrive. To do this, you can simply use: if Text.StartsWith(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[Path],"http") then (code above) else (code from my video). The same works with other "common" SP folders as well if you have access. P.S. Here is one more Wyn's video on this topic with more in depth explanation: kzbin.info/www/bejne/Y4moaHt7j8eEn7s

[@Prafful000]

Very Nice hack... It would have been great only if you spoke better english for me to understand 😢

[@howtolearnexcel]

Thanks for the feedback! I’m sorry if my English made it a bit hard to understand. I’ll do my best to improve, step by step! Hopefully, the latest videos will be easier to follow. Appreciate your patience!

[@Prafful000]

@howtolearnexcel Once again Thank you for this game changer excel hack... Will watch the video a couple of times more and learn it 👍