Power Query Source from Relative Paths

Hanna_Taskaieva 

If I understood correctly you query the files from the folder within the file which is in the same folder. Folder could be on local drive or on OneDrive for Business. If so, such construction shall work

let
    myOneDrive = "https://fcbc1-my.sharepoint.com/personal/account",

    folder = Table.SelectRows(
        Excel.CurrentWorkbook(),
        each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
    Source =
        if Text.StartsWith(folder, "https")
        then 
            let
                oneDrive = SharePoint.Files(myOneDrive, [ApiVersion = 15]),
                myFolder = Table.SelectRows(oneDrive, each [Folder Path] = folder)
            in myFolder
        else Folder.Files(folder)
in
    Source

First time you run it query asks you for data privacy. You may ignore it or set the same privacy (e.g. Organizational ) for all data sources - current workbook and files in folder.

Above works in my environment, I only used my actual "myOneDrive". 

If the workbook in which you collect all data is in different from source files folder, that is another story.

tfmeier2235 

Looks like I'm working on something similar.  Have a look at this snippet and see if it gives you any ideas.  The gist here is I split the text, delete some bottom items, and combine it again.

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    #"Split Text" = Text.Split(FilePath, "\"),
    #"Removed Bottom Items" = List.RemoveLastN(#"Split Text",3),
    #"Combine Text" =  Text.Combine(#"Removed Bottom Items","\"),
    FullPath = #"Combine Text" & "\Projects.xlsm",
    Source = Excel.Workbook(File.Contents(FullPath), null, true),
    Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"