Power Query variable

[gino59]

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]

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

 

[gino59]

Awesome, Scottsen - thanks!

 

[ChrisWebb]

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]

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]

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

Chris

 

[gino59]

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]

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

 

[gino59]

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]

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