none
Dynamic referencing to a folder containing files to query RRS feed

  • Question

  • Hi all, 

    Still new to power query and M coding... I'm trying to make some dynamic referencing in a power query but so far no success. 

    Situation: I need to clean more than 50 identical (in format) excel input templates in power query and consolidate them into 1 clean query that I can then use as input to power pivot tables/charts. These templates are saved in a  folder named "C:\Users\FFFF\Documents\WORKING_FILES\Data". I run a function in Power Query that queries all identical files stored in this folder, repeating all the steps to clean each templates. 

    Issue: I'm not the only one at work who would like to use the consolidation tool, but if I share it then people have to manually open power query, the advanced editor and change the folder name. I'd like to make an easier solution where in the consolidation excel tool itself the folder path is stored in a cell. The end user simply updates that cell to re-link the query with the folder containing all the templates to consolidate. 

    The M code I have is the following

    _____

    let
        Source = Folder.Files("C:\Users\FFFF\Documents\WORKING_FILES\Data"),
        #"Added Custom" = Table.AddColumn(Source, "GetContentColumn", each fctGetContent([Folder Path]&[Name])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"GetContentColumn"}),
        ...
    in
        #"Filtered Rows"

    ____

    How can I modify the Source line to point the cell with the updated folder path? 

    Thanks!


    Wednesday, May 9, 2018 2:18 PM

Answers

  • 1) Name the cell you want to use for the folder path FolderPath
    2) Create a new query from the cell. Click the cell and select Data-->From Table/Range
    3) In the query editor, remove the Promote Headers and Changed Type steps
    4) Add a new step: Path = Table.FirstValue(Source)
    5) You can then use the FolderPath query name as a parameter in your main query

    Your FolderPath query should look like:

    let
        Source = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content],
        Path = Table.FirstValue(Source)
    in
        Path

    Wednesday, May 9, 2018 3:51 PM

All replies

  • You can create a new query, e.g. Path, which would be:

    let
        Source ="C:\Users\FFFF\Documents\WORKING_FILES\Data"
    in 
        Source

    and then modify the second row of existing syntax to something like:

    Source = Folder.Files(Path),
    you may also try with text Parameter, but I think it may require changing the security settings, otherwise it can give the "rebuild this data combination" error

    Wednesday, May 9, 2018 3:18 PM
  • 1) Name the cell you want to use for the folder path FolderPath
    2) Create a new query from the cell. Click the cell and select Data-->From Table/Range
    3) In the query editor, remove the Promote Headers and Changed Type steps
    4) Add a new step: Path = Table.FirstValue(Source)
    5) You can then use the FolderPath query name as a parameter in your main query

    Your FolderPath query should look like:

    let
        Source = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content],
        Path = Table.FirstValue(Source)
    in
        Path

    Wednesday, May 9, 2018 3:51 PM
  • I had this problem.

    when trying to develop reports which use Power Query to pull, combine, merge meta data, group data to reduce data size.  Load as connection only and then create a standard pivot table and connect to the final output query.

    I've done this for many people in the my company now.  There a number of solutions, my preferred is as follows...

    1) - Use shared network drives/servers, when mapped to a computer typically it will be noted like yours 

           C:\ etc... (but another computer this might be drive D etc) hence the problem.

    Power Query source paths can take IP address numbers and network names.  If you have shared network drive or server via your IT dept. you can change the source path to these, an example might be...

    \\Master Data Server\Mr A Persons Data Folder or \\01.01.0.1\Mr A Persons Data Folder

    Provided the users have access rights, this will work for all users (no changes needed), you may get the security warning but this is a one time issue, and quickly dealt with.

    Friday, May 11, 2018 10:04 AM
  • The above results in an error "Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
    Friday, December 7, 2018 12:16 PM
  • The above results in an error "Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

    Then don't make it a separate query. Instead do:

    let
       QryParam = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content],
       FilePath = Table.FirstValue(QryParam),
       Source = Folder.Files(FilePath),
       …

    Friday, December 7, 2018 4:22 PM
  • The above results in an error "Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

    Then don't make it a separate query. Instead do:

    let
       QryParam = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content],
       FilePath = Table.FirstValue(QryParam),
       Source = Folder.Files(FilePath),
       …

    Thank you!  This helped a lot and worked perfectly.
    Sunday, July 14, 2019 4:50 AM