Relative source file referencing in Power Query-CodePudding

I have been using Power Query to connect two excel spreadsheets together. One of the workbooks is located in a root folder, whereas the other is in a folder, located in the root folder. I have noticed that the source file path for the linked workbook is in absolute form. If these files get moved around, which they do, it will always try and reference the original source file. How can I use a relative source reference in the M script to avoid this issue? I have included the M-script below. The same files are copied to a new sales year, and I would like the connections to just look in relative locations rather than absolute.

Any help is appreciated.

let
    Source = Excel.Workbook(File.Contents("X:\Sales-2021\Source\Tallys.xlsx"), null, true),
    #"Tally_Sheet" = Source{[Item="Tally",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Tally_Sheet", [PromoteAllScalars=true]),
    #"Filter rows to FROM and TO dates" = Table.SelectRows(#"Promoted Headers", each [Date] >= from_date and [Date] <= to_date)
in
    #"Filter rows to FROM and TO dates"

CodePudding user response：

More specifics would be useful.

For example, if you know the path of the Root folder and the Name of the file and/or parts of the path, you can use the Folder Connector to obtain that file:

For example:

let
    root = "C:\Users\ron\Documents",
    subFolder="2020",
    fileName = "Profit and Loss",

    Source = Folder.Files(root),
    #"Filtered Rows" = Table.SelectRows(Source, each 
        Text.Contains([Folder Path], subFolder) 
        and Text.Contains([Name], fileName))
        
in
    #"Filtered Rows"

Will return all files containing "Profit and Loss" in

"C:\Users\ron\Documents*\2020*"

You can construct your variables however you wish.

For example, if you knew the subfolder would always contain the current year, you could use:
subFolder= Number.ToText(Date.Year(DateTime.LocalNow())),

Given what you have written, you might use, for example:

   root = "X:\",
    subFolder= "Sales-" & Number.ToText(Date.Year(DateTime.LocalNow())),
    fileName = "Tallys",

Once you have the correct file path/name you can feed it into your working query in place of the absolute path.

If you cannot hard code enough of the file name/path to make it unique, you can feed it to the query by using either a parameter or a named range in the main workbook, but if you can, then it may be just a matter of searching through the data returned by the Folder Connector in PQ.

CodePudding user response：

Give a cell a range name, like location in excel and put your relative filepath in that range, either directly or with a formula. Sample formula for that below

Then in powerquery, in home ... advanced editor ... add a formula that refers to that range name, similar to this:

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

Change hard coded filepath to incorporate your filepath variable and then append the filename, similar to:

Source = Excel.Workbook(File.Contents(Loc&"Tallys.xlsx"), null, true),

In the location range, you can use

=LEFT(CELL("filename"),FIND("@",SUBSTITUTE(LEFT(CELL("filename"),FIND("\[",CELL("filename"))-1),"\","@",LEN(LEFT(CELL("filename"),FIND("\[",CELL("filename"))-1))-LEN(SUBSTITUTE(LEFT(CELL("filename"),FIND("\[",CELL("filename"))-1),"\","")))))

to get from

\\Corporate\enterprise\CK\MARKETING PLANS\FY-22\Daily Meeting\[linked.xlsx]Weekly

\\Corporate\enterprise\CK\MARKETING PLANS\FY-22\