PowerQuery - Dynamic Path

[Paddo2010]

Hi ,

I have been able to create my first dynamic path linked to a folder. Still working to understand the logic involved in the the creation process.

My dynamic path seems to work but only if I update the name of the file in the advanced editor but I have seen people simply dragging and drop the new file link to the path they created and refresh to work without going back to the editor.

What am I doing wrong please? Thank you

 

[johnny51981]

Here is my process.

Step 1: Create a tab specific to the settings/lookup tables needed for Power Query use.
Step 2: On this settings tab, populate a cell with =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1) . This formula will return the full network path for where the your file is currently located (based on most recent save)
Step 3: Name the cell in the Name Box to the left of the Formula Bar. I usually use Filepath
Step 4: In your Power Query, add in the following code:

If Data Source is an Excel File

let
    FilePath = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath & "[Data Source File Name.xlsx"), null, true)
in
    Source

If Data Source is a CSV File

let
    FilePath = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(Filepath & "[Data Source File Name.csv"))
in
    Source

 

[Paddo2010]

I will try this, thank you so much