Forum Discussion

ChristopherDuncan's avatar
ChristopherDuncan
Copper Contributor
Jul 19, 2021
Solved

Power Query Relative Path for OneDrive and Other Users

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-fr...
BI & Data Analysis
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 19, 2021

    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.

NG_CAX's avatar
NG_CAX
Copper Contributor
Jun 02, 2023

ChristopherDuncan 

 

I am trying to pull in files from my company sharepoint environment and have followed the code to the T

 

Here it is

 

let
FilePath=Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FullPathToFile1 = FilePath & "Outline_WAG_WITHOUT_CONNECTION.xlsx",
Source = if Text.Start(FullPathToFile1,4) = "http"
then Excel.Workbook(Web.Contents(FullPathToFile1),null,true)
else Excel.Workbook(File.Contents(FullPathToFile1),null,true),
 
But I can't seem to pull the data in. Is there an way you can still help with this?
 
Best,
Neha

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 02, 2023

NG_CAX 

It depends on how did you defined named cell FilePath within the grid. For example, that could be

=TEXTBEFORE( CELL("filename"), "/",-1) & "/"

Resources