Forum Discussion

Magnus Vegem Dahle's avatar
Magnus Vegem Dahle
Brass Contributor
Jun 20, 2018
Solved

Power Query Source from Relative Paths

Hi All,   I am making an analysis tool that uses two Excel file sources via Power Query. The tool work well, but as soon as the file and source files are moved to another location it stops working ...
excel
Formulas and Functions
Get & Transform
Power Query
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 20, 2018

    Hi Magnus,

     

    You may find the current file path putting in any cell of your workbook the formula

    =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

    name this cell somehow, let say "FilePath" and generate absolute path in query like

    <prev step>
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FullPathToFile1 = FilePath & "Name1.xlsx"
<next step>

     

WhateverName's avatar
WhateverName
Copper Contributor
Feb 09, 2022

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"

 

Keen_to_learn_but_slow's avatar
Keen_to_learn_but_slow
Copper Contributor
Mar 23, 2022

WhateverName 

So I'm really struggling with what I believe is a similar issue, but cant to get your snippet to work for me. Any pointers gratefully received!  This file will be downloaded by multiple users to a predefined location in a folder tree, but the top level file path will change for two reasons

1. The user name and

2. The Scheme name the file lives in. 

So... The Data I want to query lives in:

C:\Users\USER_Name\CDE_NAME\ORG_NAME\SCHEME_NAME\01.0 Project Standards\05.0 Information Management\96.0 System Configuration\CS614.xlsx

 

The file that I want to query then display the data lives in:

C:\Users\USER_Name\CDE_NAME\ORG_NAME\SCHEME_NAME\06.0 Design\03.0 The Delivery Team \15.0 Design - Civils\06.0 DOC

 

So in my head I need Power query identify the file path in use for itself,  truncate the file path after 

C:\Users\USER_White\CDE_NAME\ORG_NAME\SCHEME_NAME and then append that truncated text with the "\01.0 Project Standards\05.0 Information Management\96.0 System Configuration\CS614.xlsx" 

 

Can anyone give me any pointers as this is blowing my tiny brain!

Resources