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.

WCooke42's avatar
WCooke42
Copper Contributor
Sep 01, 2023

SergeiBaklan 

 

I used the CELL("filename",$A$1) function above to obtain my file path.  This returned a relative path with forward slashes.  Power Query error said that it needs to be an absolute path, so I replaced the forward slashes with back slashes.  I am trying to grab my data from a folder with multiple files and the source function keeps failing with the following code:

 

let

FilePath = Excel.CurrentWorkbook(){[Name="MyFilePath"]}[Content]{0}[Column1],

FullPathSales = FilePath & "Data Files\Sale Detail",

Source = Folder.Files(FullPathSales), ((folder path must be valid absolute path))

 

I have tried some of the other functions for the Source, 

Excel.Workbook(Web.Contents(FullPathSales), null, true) ((Format Error: Host Name cannot Parse)) & Excel.Workbook(File.Contents(FullPathSales), null, true) ((Must be valid absolute path)). 

 

The FullPathSales result is:

https:\\.....my.sharepoint.com\.....\Documents\Reports\Sales Metrics\Tasting Room\Data Files\Sale Detail\

 

copy/paste the link in my browser works fine, both forward slash and back slash addresses. 

 

What am I doing wrong?

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 01, 2023

WCooke42 

Perhaps you have other names. Please try

let
    Source = Table.SelectRows(Excel.CurrentWorkbook(), each ([Name] = "FilePath"))[Content]{0}[Column1]{0},
    FullPathSales = Source & "test/testflow 01.xlsx",
    getSales = Excel.Workbook(Web.Contents(FullPathSales), null, true)
in
    getSales

assuming we are in root, and change subfolder/filename on your actual ones.

 

Resources