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

 

[ljonmaire]

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

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