Results 1 to 2 of 2

Thread: Parameter for Data Source when Data Source is a Folder

  1. #1
    Neophyte Caleb_RB's Avatar
    Join Date
    May 2018
    Posts
    1
    Articles
    0
    Excel Version
    Excel 2016 Office 365

    Parameter for Data Source when Data Source is a Folder



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I am trying to get a query that references a folder to change the file path with a parameter. I am wanting to do this so that it can be shared with several people and they can simple change the name in a cell and it will change the file path to their computer. However when I attempt to replace the file path with the parameter I get an error (attached below).

    When doing this with anything other than a folder I can go to the source step and use the advanced option to add a parameter to the source, but I have not found the equivalent solution to this.

    Any help with this would be greatly appreciated.

    Caleb
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Error.PNG 
Views:	10 
Size:	5.4 KB 
ID:	7943   Click image for larger version. 

Name:	PQ Issue.PNG 
Views:	13 
Size:	52.3 KB 
ID:	7944   Click image for larger version. 

Name:	AE for Query.PNG 
Views:	13 
Size:	55.0 KB 
ID:	7945   Click image for larger version. 

Name:	AE with Error.PNG 
Views:	12 
Size:	55.0 KB 
ID:	7946  

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.

    One way

    I like to set up ability to do multiple parameters. So (a) create function Get Value
    Code:
    // name this function GetValue
    (ParameterName as text) =>
    let
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")
    in
    Value
    (b) Create 2x2 table named "Parameters" in excel with column headers of "Parameter" and "Value" (c) In the first row under column headers, put "Path" and "c:\temp" (excluding quotes) as the parameter name and value
    (d) Where you would normally have the path in any query, replace that path with GetValue("Path") so that
    Code:
    Source = Folder.Files("c:\temp"),
    becomes
    Code:
    Source = Folder.Files(GetValue("Path")),
    Any one can edit the parameter table to do a live change to the linked query paths
    Last edited by horseyride; 2018-05-05 at 06:41 PM.

Tags for this Thread

Posting Permissions

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