Follow on to Power Query issue

[gino59]

Hi all,

I'm back again with a revisit of my earlier Power Query question which Chris Webb did an awesome job of answering!

The solution was to find the newest file in the folder and it works great - until some knucklehead decided to put non-relevant files in the same directory.

The thread was here... http://www.mrexcel.com/forum/power-bi/813084-power-query-variable.html

Is there a way to look for the newest version of a file that will always start with "ABC"? The files I want would be "ABC-January 2014.xlsx" and "ABC-February 2014.xlsx" and so on...

Many thanks!!
Gino

 

[scottsen]

Assuming, you ended up with a table of files... I suspect you can use Table.FindText? (which given a table and pattern, returns a table of rows that match the pattern).

 

[gino59]

Thanks, scottsen! That makes sense (sort of). Chris' code gets me the newest file in the folder. I'm just not sure where to specify the text/wildcard.

let
    //Choose folder
    Source = Folder.Files("\\abcshare.abcare.com\pso\education\businessoperations\Document Library\Pricing\PSO EDU Price Lists\"),
    //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 modified", Order.Descending}}),
    output = #"Sorted Rows"{0}[Name]
in
    output

So somewhere in that query, I'm stuck with figuring out what the syntax would be to ensure that the filtered list only includes xlsx files that start with "ABC Education Pricelist - ". After the hyphen will be January 2014, February 2014, etc. That's supposed to be the standardized naming convention (of course, some knucklehead will probably change that some day soon!).

Thanks for taking a look at this!

Cheers,
Gino

 

[gino59]

I think I figured this out!! It seems to do what I need...

let
    //Choose folder
    Source = Folder.Files("\\abcshare.abcware.com\pso\education\businessoperations\Document Library\Pricing\PSO EDU Price Lists\"),
    //filter out all but Excel files that start with ABC - "
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "PSO Education Pricelist - ")),
    //sort in descending order by date created
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    output = #"Sorted Rows"{0}[Name]
in
    output

Does that look right? The first filtered rows makes sure the table only returns Excel files and the second makes sure it only returns files that start with ABC - . I'm assuming the filters aren't mutually exclusive but rather sequentially combined. It gives an Expression.Error: Duplicate initializer named Filtered Rows, so I guess that won't fly... If I just use one, it goes fine.

Many thanks!!
Gino

 

[scottsen]

Ya, that looks great. Only thing you need to fix is that you are assigning #"Filtered Rows" twice. That is just an identifier, you can call it whatever you want.

#"Just XLSX" = ...
#"Just PSO" = ...

Just make sure you feed the right (2nd) table into Table.Sort.

 

[gino59]

Ah - didn't know that. Okay, changed those two lines to "Filtered Extension" & "Filtered Name" and all seems well in the world!

Thanks so much for pointing me in the right direction. Just had the parcel dude deliver Chris' Power Query book - digging in and hope to learn how to wrestle yet another aspect of Excel to the mat!

Cheers,
Gino