# Power Query variable



## gino59 (Oct 21, 2014)

Can anyone tell me how to create and pass a variable in Power Query?

Here's the file I'm trying to retrieve but I'd like the file name to change based on the newest file in the folder.  The files will all be named the same with the exception of the month and year part.  Here's the Power Query code:

```
let
    Source = Excel.Workbook(Web.Contents("https://xxshare.xxxxx.com/pso/education/businessoperations/Document Library/Pricing/PSO EDU Price Lists/PSO Education Pricelist - November 2014.xlsx")),
    #"Education Pricelist_Sheet" = Source{[Item="Education Pricelist",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(#"Education Pricelist_Sheet")
in
    #"First Row as Header"
```

What would be ideal is to pass a variable based on the newest file in the folder that changes the requested file to PSO Education Pricelist - December 2014.xlsx.

Is that possible?

Many thanks!!
Gino


----------



## scottsen (Oct 21, 2014)

I'll page an expert for you.  hold please.


----------



## gino59 (Oct 21, 2014)

Awesome, Scottsen  - thanks!


----------



## ChrisWebb (Oct 21, 2014)

Yes, this is all possible. First of all, you need a Power Query query to find the name of the most recent Excel file in the folder. Here's something that should do roughly what you want:


```
let
    //Choose folder
    Source = Folder.Files("C:\Users\Chris\OneDrive\Public"),
    //filter out all but Excel files
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    //sort in descending order by date created
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
    //combine folder path and file name
    #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    //get the path and file name from the first row - the name of the newest file
    Merged = #"Merged Columns"{0}[Merged]
in
    Merged
```

Set the name of this query to be MyFileName. You can then use the name of the query as a variable in your main query, like so:


let    Source = Excel.Workbook(Web.Contents(MyFileName)),    #"Education Pricelist_Sheet" = Source{[Item="Education Pricelist",Kind="Sheet"]}[Data],    #"First Row as Header" = Table.PromoteHeaders(#"Education Pricelist_Sheet")in    #"First Row as Header"</pre>
The important thing to notice is that the first query above returns text, not a table like most Power Query queries. This means you can use the text as the file name in the second query.

HTH,

Chris


----------



## gino59 (Oct 21, 2014)

Many thanks, Chris.  I created the first query as you posted and the only thing I changed was the source= to the folder on my machine I'm working on.  That came through fine.  However, when I changed the main query (to the sharepoint site) to "... Web.Contents(MyFileName)), I get an error message:  Formula.Firewall: Query 'EDU_PriceList' (step 'First Row as Header') references other quereies or steps and so may not directly access a data source.  Please rebuild this connection.

Here's the "variable" query:

```
let    //Choose folder
    Source = Folder.Files("C:\Users\gino\Desktop\EDU Sales Quote\"),
    //filter out all but Excel files
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    //sort in descending order by date created
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
    //combine folder path and file name
    #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    //get the path and file name from the first row - the name of the newest file
    Merged = #"Merged Columns"{0}[Merged]
in
    Merged
```

and here are the other two queries (main queries):

```
let    Source = Excel.Workbook(Web.Contents(MyFileName)),
    #"Education Pricelist_Sheet" = Source{[Item="Education Pricelist",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(#"Education Pricelist_Sheet")
in
    #"First Row as Header"
```


```
let    Source = Excel.Workbook(Web.Contents(MyFileName)),
    #"Education Channels Pricelist_Sheet" = Source{[Item="Education Channels Pricelist",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(#"Education Channels Pricelist_Sheet"),
    #"First Row as Header1" = Table.PromoteHeaders(#"First Row as Header")
in
    #"First Row as Header1"
```

Should I be setting the "variable" source to the same as what was in the original main query (the web address)?

Thanks much!
Gino


----------



## ChrisWebb (Oct 21, 2014)

Can you try turning on the Fast Combine option?
https://support.office.com/en-US/Ar...4d-359e-4b28-bc72-9bee7900b540#__toc381720831

Chris


----------



## gino59 (Oct 21, 2014)

Thanks, Chris - did that and now get
{Expression.Error] The key did not match any rows in the table.

Stumped...

I really appreciate the help!
Cheers,
Gino


----------



## ChrisWebb (Oct 22, 2014)

Which query do you get this error from? Is it from the variable query?


----------



## gino59 (Oct 22, 2014)

Thanks, Chris - I get the latest error when refreshing either of the two main queries.  The variable query simply returns one line (the folder specified and the newest file there).  That folder is on my machine.  The two main queries need to find the latest file on the sharepoint.

Hope that makes sense.

Thanks!
Gino


----------



## ChrisWebb (Oct 22, 2014)

OK, well if your two queries need to find the file on SharePoint and the variable query returns the name and path of the file on the local drive, that is probably the cause of the problem. What you should do is alter the variable query so that it only returns the filename, rather than the full path, and then in your main queries use that to generate the location of the file in your SharePoint.

Chris


----------



## gino59 (Oct 21, 2014)

Can anyone tell me how to create and pass a variable in Power Query?

Here's the file I'm trying to retrieve but I'd like the file name to change based on the newest file in the folder.  The files will all be named the same with the exception of the month and year part.  Here's the Power Query code:

```
let
    Source = Excel.Workbook(Web.Contents("https://xxshare.xxxxx.com/pso/education/businessoperations/Document Library/Pricing/PSO EDU Price Lists/PSO Education Pricelist - November 2014.xlsx")),
    #"Education Pricelist_Sheet" = Source{[Item="Education Pricelist",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(#"Education Pricelist_Sheet")
in
    #"First Row as Header"
```

What would be ideal is to pass a variable based on the newest file in the folder that changes the requested file to PSO Education Pricelist - December 2014.xlsx.

Is that possible?

Many thanks!!
Gino


----------



## gino59 (Oct 22, 2014)

Thanks for sticking with this, Chris but now I'm confused by your last reply.  I don't know how to have the variable query return the newest file without specifying the file name.

The first main query gets one table from the newest file and the second query gets a different table from the newest file.  The file names will always be the same with the exception of the month and year.  For example, "EDU Price List - October 2014" gets replaced by EDU Price List - November 2014" and so on.

The two main queries work well because the query has the file name spelled out (say for October 2014).

I guess I'm just not sure how to alter the variable query so that it only returns the filename.

Really appreciate your help!

Cheers,
Gino


----------



## gino59 (Oct 22, 2014)

Ok, so I changed the path name in the variable query to the same folder that the files are stored in.  The address is a https:// address.  Run the variable query and get "DataFormat.Error: The supplied folder path must be a valid absolute path."

Not quite sure what that means!

Ugh...


----------



## ChrisWebb (Oct 22, 2014)

No, sorry, what I was suggesting was that you change the variable query so that it pointed to the folder on your desktop but only returned the filename and not the full path plus filename. For example, rather than return c:\my directory\myfile.xlsx it would return just myfile.xlsx. The query would be something like this:


```
let
    //Choose folder
    Source = Folder.Files("C:\Users\Chris\OneDrive\Public"),
    //filter out all but Excel files
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    //sort in descending order by date created
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
    output = #"Sorted Rows"{0}[Name]
in
    output
```

Your main query would then be:


```
let    
    Source = Excel.Workbook(Web.Contents("https://xxshare.xxxxx.com/pso/education/businessoperations/Document Library/Pricing/PSO EDU Price Lists/" & MyFileName)),
    #"Education Pricelist_Sheet" = Source{[Item="Education Pricelist",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(#"Education Pricelist_Sheet")
in
    #"First Row as Header"
```


----------



## gino59 (Oct 22, 2014)

Ok, I think I'm getting closer - thank you so much!  Where I'm stuck now is that the variable query is referencing a folder on my desktop when I think I need it to reference the sharepoint site where the files to be queried reside.  When I run the variable query it returns the newest xlsx file from the desktop directory.  However - they are not the same files on the sharepoint I'm trying to query.

I'm working in a desktop folder on a file called Pricing.xlsm which comes up as the newest file from the variable query.  then when running the main queries, I get the [DataSource.Error] Web.Contents failed to get contents from 'https://xxx....xlsx' (404): NOT FOUND.

Would seem to me that the main query is now looking for the Pricing.xlsm file in the network folder which of course is actually in my desktop folder.

I hope that makes sense - I guess I really need to cram  more on this stuff!! 

Many thanks, Chris!


----------



## gino59 (Oct 22, 2014)

GOT IT!!!  Hey Chris - I think I've actually figured it out with your awesome help!!!  The variable query is now querying the network folder and getting the newest file as the file name.  The main queries I modified as you instructed.

Turns out in the variable query, it doesn't like querying an https directory structure but once I converted that to the correct network address format (think //https: vs \\vmxxx\), it all worked and worked well.

I even tested it out by creating a new junk file in the network folder, ran the variable query and watched it return the junk.xlsx file name!

Wow - thank you and thank you and thank you!  I so do appreciate your kind help!



And at the risk of being greedy - can I please ask one more Power Query question?  If I send this workbook to someone who does not have PowerQuery installed, will it still work?  I've written a macro that just refreshes the listobject (queried table) on each sheet.  Not sure if the users will have to go install Power Query.  Do you know?

Again - thanks so much!

Cheers,
Gino


----------



## ChrisWebb (Oct 23, 2014)

Great!

To answer your second question, no it won't work unless the other user also has Power Query installed.

Chris


----------



## gino59 (Oct 23, 2014)

Yeah, that's what I thought.  Well, it's working and again - thanks much!  

Just ordered up Power Query for Power BI and Excel from Amazon!  Can't wait!

Cheers,
Gino


----------



## scottsen (Oct 23, 2014)

Power Query for Power BI and Excel: Chris Webb: 9781430266914: Amazon.com: Books


----------



## ljonmaire (Oct 24, 2014)

scottsen said:


> Power Query for Power BI and Excel: Chris Webb: 9781430266914: Amazon.com: Books



Hey guys, can I jump in here? I have what I think is a somewhat simple question, though related to this discussion. I'm totally stuck. All I'm trying to do is pull in (and manipulate via PowerQuery magic) all of the files in a folder. I defined the function below:

let GetMyFiles=(FilePath, FileName) =>

let
    Source = Excel.Workbook(File.Contents("FilePath&FileName")),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(Sheet1_Sheet)
in
    #"First Row as Header"

in GetMyFiles

I then created a query to get files from a folder, and attempted to create a custom column that called the function: fGetMyFiles([Folder Path],[Name])

I'm getting an error in this column reading:

An error occurred in the ‘fGetMyFiles’ query. DataFormat.Error: The supplied file path must be a valid absolute path.
Details:
    FilePath&FileName

You could probably answer this in your sleep, but help would be much appreciated!!

Lisa


----------



## scottsen (Oct 24, 2014)

Prolly should start a new thread.  My similiar solution:


```
let ExcelFile = (FilePath, FileName) =>
let
    Source = Folder.Files(FilePath),
    File = Source{[#"Folder Path"=FilePath,Name=FileName]}[Content],
    ImportedExcel = Excel.Workbook(File),
    Sheet1 = ImportedExcel{0}[Data],
    FirstRowAsHeader = Table.PromoteHeaders(Sheet1)
in
    FirstRowAsHeader
in
    ExcelFile
```

And then I call that like...

```
Source = Folder.Files("C:\InputFiles"),
    InsertedCustom = Table.AddColumn(Source, "GetExcelFile", each GetExcelFile([Folder Path], [Name])),
```

I only vaguely remember what/how/who/when I was doing this, but it worked at least :P


----------



## gino59 (Oct 21, 2014)

Can anyone tell me how to create and pass a variable in Power Query?

Here's the file I'm trying to retrieve but I'd like the file name to change based on the newest file in the folder.  The files will all be named the same with the exception of the month and year part.  Here's the Power Query code:

```
let
    Source = Excel.Workbook(Web.Contents("https://xxshare.xxxxx.com/pso/education/businessoperations/Document Library/Pricing/PSO EDU Price Lists/PSO Education Pricelist - November 2014.xlsx")),
    #"Education Pricelist_Sheet" = Source{[Item="Education Pricelist",Kind="Sheet"]}[Data],
    #"First Row as Header" = Table.PromoteHeaders(#"Education Pricelist_Sheet")
in
    #"First Row as Header"
```

What would be ideal is to pass a variable based on the newest file in the folder that changes the requested file to PSO Education Pricelist - December 2014.xlsx.

Is that possible?

Many thanks!!
Gino


----------



## ljonmaire (Oct 24, 2014)

scottsen said:


> Prolly should start a new thread.  My similiar solution:
> 
> 
> ```
> ...




Oooooh my...I wasted hours over quotes around "FilePath" when using the variable. Thanks!!!


----------

