Import Dynamic Workbook

[Belebala]

Hello,

I'm new to power query and would like some help on import workbook with dynamic file location.

I have a an excel file with power query saved under W:\Excel\Jan and the source file is saved under W:\Excel\Jan\Source. I copy the same file to another location for the next month (W:\Excel\Feb) from a new source file W:\Excel\Feb\Source.

I don't want to change my source from my queries every month, is there anyway to have a dynamic source location?

I have googled and it suggested to create a parameter table with the value of the source location =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) and create a ParameterValue query of = Table.TransformColumnTypes(Source,{{"ParameterValue", type text}}). Now I'm stuck. In my query, I changed my source to the following but it is giving me an error or "The important ParameterQuery matches no exports":
Let
Source = Excel.Workbook(File.Contents(ParameterQuery[ParameterValue]{0})),

I am not a tech savy and have no idea how it should work. Much appreciated if anyone could help.

Thanks.
Belebala

 

[Paul_Christie]

There are several ways to do what you want, the following is the method that Ken Puls uses in his excellent book M is for (Data) Monkey
In the workbook where you have your PowerQuery, create an Excel Table, name it 'ParameterTable' with two columns 'Parameter' and 'Value' and add a parameter in the first row of the table.
Enter FilePath in the first row of the Parameter column and enter a value path name (e:\pq) in the first row of the value column.
Create a blank query and select 'View' and 'Advanced Editor'
Past the following
// fnGetParameter function written by Ken Puls, FCPA, FCMA, MS MVP (Excel)
// Original source can be found at the following URL:
// http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

// To use the function you must have a table called "Parameters" in your workbook
// The header row must contain a "Parameter" and a "Value" column (with those names)

// Data rows should list the paramter name in the first column and the value of that
// parameter in the second column

// Provided you save this query under the name "fnGetParameter", you can then reference
// it from other queries as follows:
//
// =fnGetParameter("your_parameter_name")

// One useful sample to return the current folder:
// Parameter Name: File Path
// Parameter Value: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
// Call as "fnGetParamater("File Path")

(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

save and name the query as fnGetParameter in your existing query edit the existing source line so it looks like

Source = Folder.Files(fnGetParameter("FilePath")),

You can now use the table created above as a 'variable' data source.

Paul Christie

 

[Belebala]

Hi Paul Christie,

After I replaced the code of fnGetParameter, it asked me to invoke my function and the function return null. Is it normal?

 

[Paul_Christie]

If you enter 'FilePath' and then press the Invoke button it should return the value you have for that parameter in the table.

Paul

 

[Belebala]

Ok, I have invoke the File Path as a parameter. Now I get an error when I tried to add a custom column of File Path in MyTable query. I'm sorry for so many questions.

 

[Paul_Christie]

Is it possible for you to post the workbook so I can look at it?

 

[Belebala]

Sure. That would be very helpful if you could review it.

Thank you.

 

[Paul_Christie]

The fnGetParameter bit works perfectly it's the next bit that is causing you the problem. I'll see what I can do with one of my examples. What's your location from a time zone point of view relative to the UK?

 

[Belebala]

My time zone is GTM-6.

Thanks a bunch for looking into this for me.

 

[Paul_Christie]

I've attached a full example using anonomised data

View attachment PQ Demo.xlsx

Hope this helps

 

[Belebala]

I got a question, why my query fnGetParameter display the table icon not function icon? Did I do something wrong?

 

[Paul_Christie]

Without being able to watch what you were doing I can't be sure. What I did was create a blank query, pasted the function code in, named the function as fnGetParameter and saved it as connection only.

Paul

 

[Belebala]

I think I got ParameterTable query working. How do I replace the source file if my import file is an excel workbook?

I tried this, but it said token identifier expected.
Source = Excel.Workbook(fnGetParameter("FilePath")&fnGetParameter("FileName")),null, true),

 

[Paul_Christie]

The source line needs to look like this

Source = Excel.Workbook(File.Contents(fnGetParameter("FilePath")&fnGetParameter("FileName")), null, true),

Paul

 

[Belebala]

Now I get the error of :
Expression.Error: We cannot convert the value "D:\PowerQuery..." to type Function.
Details:
Value=D:\PowerQuery\
Type=Type

Any idea?

 

[Paul_Christie]

Is it possible to post the workbook with the query and the workbook with the data so i can see both ends of what is going wrong?

Paul

 

[Belebala]

My query is saved under the Dynamic File Test and the data is coming fom the Book1 workbook.

 

[Paul_Christie]

It looks like it had something to do with how you created fnGetParameter

View attachment Book1.xlsxView attachment Dynamic File Test.xlsx

Have you got Ken Puls book M is for (Data) Monkey. It really is worth every penny it will cost you.

Paul

 

[Belebala]

I will definitely grab Ken Puls' book.

I get it now, if I invoked the fnGetParameter query, it will turn into the query table icon. If I don't invoke it, it will work.

Thank you very much Paul for solving my problem. I appreciate your effort!

Belebala

 

[El Cid]

let
path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],
filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],
address = Text.Combine({path,filename}, ""),
Source = Excel.Workbook(File.Contents(address), null, true),

1. First you make two named ranges in a separate worksheet tab I name PARAM: PATH and FNAME using two separate cells, one right after the other.

1. Next, place these formulas in each of these named ranges

1. In PATH and put this formula (which gets the path of the current spreadsheet) in it:

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

1. FNAME and put this formula (to get the name of the current spreadsheet) in it if you are pulling data from it to use in your current spreadsheet.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

OR

Just type in the name of the file you want to use.

1. Then, to transfer it to Power Query as a constants, you insert these lines in the PQ editor:

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],
filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

It loads the named ranges as a (single value) table and gets the first row ({0}) of Column1, i.e., the path and file name of the current sheet. This way, you can load the file using a relative address and you "current file" can change names.

1. Now you assign the variable "address" to the actual name of your import file combined with the "path" added in step 2.

address = Text.Combine({path,filename}, ""),

OR, if you did not use FNAME

address = Text.Combine({path,"QB QIP.csv"}, ""), //Note that “path” will be the path to the current spreadsheet. If the files are in the same folder, then their paths are “path” as well

1. This final steps brings it all together and will import the desired file

Excel File: Source = Excel.Workbook(File.Contents(address), null, true),
CSV File: Source = Csv.Document(File.Contents(address),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),