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

 

[gino59]

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]

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]

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]

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]

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]

Great!

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

Chris

 

[gino59]

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]

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

 

[ljonmaire]

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]

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