Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Dynamic Query path (Power Query)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Query path (Power Query)

    Hello,

    Is there a way to set dynamic querry paths in Excel? Example:
    I have a Master file which uses connections to 3 files which all have the same structure. The reason there are 3 files is because there are 3 people who must fill in their data. Then I created a querry in this Master file with links to those 3 files. My files are saved, let's say in C:/Documents. All in the same folder (both input files and Master file). If I copy all the files from C:/Douments to D:/Working files, then the Master file stops working as Querries are looking for data in C:/Documents folder.
    So the question is whether there's a possibility to use dynamic file paths in querry connections so that when I copy files to different locations, the path in the querry is updated as well? All 4 files (Master file and input files) will always be in the same folder.

    Thank you!

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    No, power query can't automaticly follow your file movements. I normally place all files in 1 folder, and then use get data\file\folder. This will process all files in the one folder. You can split the query in 2 so that the first query points to the folder and the second combines the files. That way if your location changes you can just change the first query.
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    Matt, there is a trick we can use. )We can do it with a little help from excel.
    Put somwehere in your Master file this formula and give it a Name (for example your formula is in A1 - Name for A1 "FolderPath")
    Code:
    =LEFT(CELL("filename",A1),SEARCH("[",CELL("filename",A1))-1)
    Then you can use this construction in your PQ query.
    Code:
    let
        Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
        Source = Folder.Files(Path),
    ...
    ...
        LastStep = something
    in
        LastStep
    and Voila.... now your path to the folder can follow your file )

    Regards

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    Very good idea. But does this cell value refresh if you don't open the file ? eg if you save the file, close the file, move the file, then does it show the new file location or the old one. I assume the old one
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    Quote Originally Posted by billszysz View Post
    Matt, there is a trick we can use. )We can do it with a little help from excel.
    Put somwehere in your Master file this formula and give it a Name (for example your formula is in A1 - Name for A1 "FolderPath")
    Code:
    =LEFT(CELL("filename",A1),SEARCH("[",CELL("filename",A1))-1)
    Then you can use this construction in your PQ query.
    Code:
    let
        Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
        Source = Folder.Files(Path),
    ...
    ...
        LastStep = something
    in
        LastStep
    and Voila.... now your path to the folder can follow your file )

    Regards

    Hi billszysz,

    thanks for your reply. Could you please help me a little bit with tweaking the PQ Query? It's completely dark forest for me Before making any changes (and everything is working fine), I have the following sequence:
    Code:
    let
        Source = Excel.Workbook(File.Contents("C:\Users\paul\Desktop\QueryTest\DataInput.xlsx"), null, true),
        Prices_tbl_Table = Source{[Item="Prices_tbl",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, /piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
    in
        #"Changed Type"
    When I follow your suggestion, I simply replace the part of Source = .... to the one you indicated and therefore my code becomes this:
    Code:
    let
        Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
        Source = Folder.Files(Path),
        Prices_tbl_Table = Source{[Item="Prices_tbl",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, /piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
    in
        #"Changed Type"
    Doing so I immediately get this error:

    Code:
     
    Expression.Error: The key didn't match any rows in the table. Details:
    Key = Record
    Table = Table
    Could you please help me with this error? Have no clue how syntax of PQ should look like.
    Thanks!

  6. #6
    Board Regular
    Join Date
    Feb 2014
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    Quote Originally Posted by Matt Allington View Post
    eg if you save the file, close the file, move the file, then does it show the new file location or the old one. I assume the old one
    The new one ))

  7. #7
    Board Regular
    Join Date
    Feb 2014
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    b0unce, "FolderPath" is only the path to the folder and not to the file. You have to filter out your desired file

    let
    Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Folder.Files(Path),
    #"Filtered Rows" = Excel.Workbook(Table.SelectRows(Source, each ([Name] = "DataInput.xlsx")){0}[Content]),
    Prices_tbl_Table = #"Filtered Rows"{[Item="Prices_tbl",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, €/piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
    in
    #"Changed Type"

  8. #8
    Board Regular
    Join Date
    Feb 2014
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    or this code below

    let
    Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(Path & "DataInput.xlsx")),
    Prices_tbl_Table = Source{[Item="Prices_tbl",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, €/piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
    in
    #"Changed Type"

  9. #9
    Board Regular
    Join Date
    Feb 2014
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    Matt, i want to be precise.
    When You move the file to the new location, and You will not open the file in the new location, then there is an old path to the folder in the file
    But i think, this is not this case

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Query path (Power Query)

    Works like a charm (the second option)! Thank you very much!!!


    Quote Originally Posted by billszysz View Post
    Matt, i want to be precise.
    When You move the file to the new location, and You will not open the file in the new location, then there is an old path to the folder in the file
    But i think, this is not this case

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •