SOLVED

Power Query Relative Path for OneDrive and Other Users

%3CLINGO-SUB%20id%3D%22lingo-sub-2563760%22%20slang%3D%22en-US%22%3EPower%20Query%20Relative%20Path%20for%20OneDrive%20and%20Other%20Users%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563760%22%20slang%3D%22en-US%22%3E%3CP%3ELet%20me%20start%20by%20saying%20that%20I%20have%20read%20the%20posts%20about%20converting%20from%20an%20Absolute%20path%20to%20a%20Relative%20path%20in%20PowerQuery%20(primarily%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpower-query-source-from-relative-paths%2Fm-p%2F206150%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpower-query-source-from-relative-paths%2Fm-p%2F206150%3C%2FA%3E)%20which%20was%20very%20helpful.%20I've%20also%20read%20through%20other%20posts%20by%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20which%20were%20helpful%20identifying%20my%20issues.%26nbsp%3B%3C%2FSPAN%3EThe%20problem%20is%20that%20some%20of%20my%20users%20may%20be%20using%20OneDrive%2C%20others%20OneDrive%20for%20Business%2C%20and%20still%20others%20not%20using%20OneDrive%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20follow%20the%20directions%20in%20the%20above%20quoted%20link%20and%20then%20send%20the%20file%20to%20someone%20using%20OneDrive%2C%20they%20cannot%20refresh%20the%20Query.%26nbsp%3B%20The%20error%20message%20says%20there%20is%20a%20Data%20Format%20Error%20and%20that%20the%20supplied%20file%20path%20is%20invalid%20because%20it%20is%20a%20hyperlink.%20Which%20leads%20me%20to%20two%20questions%3A%3C%2FP%3E%3COL%3E%3CLI%3EHow%20do%20I%20check%20to%20see%20if%20the%20file%20address%20is%20a%20SharePoint%20link%20or%20a%20valid%20Path%20using%20the%20above%20referenced%20post%3F%3C%2FLI%3E%3CLI%3EHow%20do%20I%20convert%20a%20SharePoint%20address%20to%20the%20valid%20path%20that%20Excel%20wants%3F%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI%20would%20imagine%20it%20would%20be%20some%20form%20of%20an%20If%20then%20statement%3A%3C%2FP%3E%3CP%3EIf%20filepath%20is%20a%20Sharepoint%20link%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BUse%20this%20address%20for%20the%20file%3C%2FP%3E%3CP%3EElse%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BFollow%20the%20directions%20in%20the%20above%20post%20to%20get%20the%20filepath.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20just%20not%20familiar%20enough%20with%20the%20language%20to%20even%20begin%20to%20parse%20that%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EChristopher%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2563760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563918%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Relative%20Path%20for%20OneDrive%20and%20Other%20Users%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1107469%22%20target%3D%22_blank%22%3E%40ChristopherDuncan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20only%20parse%20CELL(%22filename%22).%20If%20it%20starts%20from%20%22https%3A%2F%2F...%22%20remove%20sheet%20name%20from%20the%20end%2C%20substitute%20%5B%5D%20around%20filename%20on%20nothing%20and%20connect%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3ESource%20%3D%20Excel.Workbook(Web.Contents(%22https%3A%2F%2Ftenant.sharepoint.com%2Fteams%2Fsite%2Fdocs%2Ffile.xlsx%22)%2C%20null%2C%20true)%2C%0A...%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BIf%20not%2C%20use%20connection%20as%20in%20mentioned%20by%20you%20thread.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563974%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Relative%20Path%20for%20OneDrive%20and%20Other%20Users%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563974%22%20slang%3D%22en-US%22%3EThanks%20Sergei%2C%20but%20you%E2%80%99ve%20found%20my%20problem.%20How%20do%20I%20write%20the%20code%20in%20power%20query%20that%20will%20switch%20from%20one%20to%20the%20other%3F%20Or%20should%20I%20do%20that%20in%20Excel%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2564047%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Relative%20Path%20for%20OneDrive%20and%20Other%20Users%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2564047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1107469%22%20target%3D%22_blank%22%3E%40ChristopherDuncan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3ESource%20%3D%20if%20Text.Start(filepath%2C5)%20%3D%20%22https%22%0A%20%20%20%20%20%20%20%20%20then%20%20Web.Contents(...)%0A%20%20%20%20%20%20%20%20%20else%20%20Csv.Document(...)%2C%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20you%20shall%20to%20clean%20filepath%20before%20(remove%20sheet%20name%20and%20brackets).%20You%20may%20do%20in%20sheet%20by%20formula%20or%20in%20Power%20Query%20by%20another%20if%20then%20else.%20Hope%20it's%20possible%20to%20find%20ready%20to%20use%20pattern%20for%20both.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2566370%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Relative%20Path%20for%20OneDrive%20and%20Other%20Users%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2566370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20You!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20anyone%20else%20who%20comes%20along%2C%20here%20is%20what%20worked%20for%20me%20to%20get%20a%20Power%20Query%20Relative%20Path%20that%20works%20for%20both%20file%20paths%20and%20One%20Drive%20addresses.%3C%2FP%3E%3COL%3E%3CLI%3ECreated%20a%20Worksheet%20titled%20Setup%20with%20this%20formula%20in%20cell%20B4%3A%3CBR%20%2F%3E%3DLEFT(CELL(%22filename%22%2CA1)%2C%20FIND(%22%5B%22%2C%20CELL(%22filename%22%2CA1))-1)%3C%2FLI%3E%3CLI%3ECreated%20a%20Defined%20Name%20for%20B4%20and%20called%20it%20FilePath%3C%2FLI%3E%3CLI%3EHid%20the%20Setup%20Worksheet%3C%2FLI%3E%3CLI%3EClicked%20in%20my%20previously%20linked%20data%20I%20imported%20with%20Power%20Query.%3C%2FLI%3E%3CLI%3EClicked%20the%20Query%20tab%20in%20the%20Ribbon%3C%2FLI%3E%3CLI%3EClicked%20Edit%20on%20the%20Query%20tab%3C%2FLI%3E%3CLI%3EClicked%20Advanced%20Editor%20on%20the%20Home%20tab%20of%20Power%20Query%20Editor%3C%2FLI%3E%3CLI%3EChanged%20the%20source%20line%20of%20my%20code%20to%20read%20as%20follows%3A%3CBR%20%2F%3EFilePath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22FilePath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%3CBR%20%2F%3EFullPathToFile1%20%3D%20FilePath%20%26amp%3B%20%22export-information%5Corders.xlsx%22%2C%3CBR%20%2F%3ESource%20%3D%20if%20Text.Start(FullPathToFile1%2C%204)%20%3D%20%22http%22%3CBR%20%2F%3Ethen%20Excel.Workbook(Web.Contents(FullPathToFile1)%2C%20null%2C%20true)%3CBR%20%2F%3Eelse%20Excel.Workbook(File.Contents(FullPathToFile1)%2C%20null%2C%20true)%2C%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E
New Contributor

Let me start by saying that I have read the posts about converting from an Absolute path to a Relative path in PowerQuery (primarily https://techcommunity.microsoft.com/t5/excel/power-query-source-from-relative-paths/m-p/206150) which was very helpful. I've also read through other posts by @Sergei Baklan which were helpful identifying my issues. The problem is that some of my users may be using OneDrive, others OneDrive for Business, and still others not using OneDrive at all.

 

When I follow the directions in the above quoted link and then send the file to someone using OneDrive, they cannot refresh the Query.  The error message says there is a Data Format Error and that the supplied file path is invalid because it is a hyperlink. Which leads me to two questions:

  1. How do I check to see if the file address is a SharePoint link or a valid Path using the above referenced post?
  2. How do I convert a SharePoint address to the valid path that Excel wants?

I would imagine it would be some form of an If then statement:

If filepath is a Sharepoint link

   Use this address for the file

Else

   Follow the directions in the above post to get the filepath.

 

I'm just not familiar enough with the language to even begin to parse that out.

 

Any help would be greatly appreciated.

 

Thanks!

Christopher

5 Replies

@ChristopherDuncan 

That's only parse CELL("filename"). If it starts from "https://..." remove sheet name from the end, substitute [] around filename on nothing and connect like

Source = Excel.Workbook(Web.Contents("https://tenant.sharepoint.com/teams/site/docs/file.xlsx"), null, true),
...

 If not, use connection as in mentioned by you thread.

Thanks Sergei, but you’ve found my problem. How do I write the code in power query that will switch from one to the other? Or should I do that in Excel?
best response confirmed by ChristopherDuncan (New Contributor)
Solution

@ChristopherDuncan 

That could be like

Source = if Text.Start(filepath,5) = "https"
         then  Web.Contents(...)
         else  Csv.Document(...),

but you shall to clean filepath before (remove sheet name and brackets). You may do in sheet by formula or in Power Query by another if then else. Hope it's possible to find ready to use pattern for both.

@Sergei Baklan Thank You!

 

For anyone else who comes along, here is what worked for me to get a Power Query Relative Path that works for both file paths and One Drive addresses.

  1. Created a Worksheet titled Setup with this formula in cell B4:
    =LEFT(CELL("filename",A1), FIND("[", CELL("filename",A1))-1)
  2. Created a Defined Name for B4 and called it FilePath
  3. Hid the Setup Worksheet
  4. Clicked in my previously linked data I imported with Power Query.
  5. Clicked the Query tab in the Ribbon
  6. Clicked Edit on the Query tab
  7. Clicked Advanced Editor on the Home tab of Power Query Editor
  8. Changed the source line of my code to read as follows:
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FullPathToFile1 = FilePath & "export-information\orders.xlsx",
    Source = if Text.Start(FullPathToFile1, 4) = "http"
    then Excel.Workbook(Web.Contents(FullPathToFile1), null, true)
    else Excel.Workbook(File.Contents(FullPathToFile1), null, true),

@ChristopherDuncan 

Great to know it works, thank you for sharing step by step instruction