Extracting Date

[Craigc3814]

Good morning, I am trying to come up with an innovative way to extract the date from a file name.

We have a vendor that bills us using a spreadsheet file every month. I store the files in a folder and have power query join them from there.

The vendor names the files 103118MDS_Detail every month (when I say every they have so far but something changes in their data file consistently and PQ hates that).

I want to extract the date from the file name and create a column called Invoice date. What I had done in the past was extract everything before the M split the columns by 2 then add the / in so that PQ recognizes it as a date.

What concerns me is the fact that in January the vendor may name the file 13118MDS_Detail therefore ruining my code.

Is there a way to extract the date in a fool proof way that would account for vendor name changes?

 

[jmacleary]

Hi there

I don't think you will be able to have a foolproof way - but you could work backwards from the M and take previous 2 as year, previous 2 as day and the remaining 1 or 2 digits as the month. I suggest you contact the vendor direct and ask them what their rules are - or you may be able to arrange a consistent convention with them - after all you are a customer!

 

[Craigc3814]

I figured them keeping the data consistent was the best way, but I wanted to check as PQ seems to get better by the day. Wanted to make sure something had not passed me by. Thanks for the help!!