Date formatting text to date

Here is how I would do this, all in Power Query before bringing it in to Power BI's data model:

• Create a data table, and ensure it has a month column and a month name column. 

It might look something like this in Power Query's advanced editor:

let
    Source = {Number.From(#date(2019,1,1))..Number.From(#date(2019,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text)
in
    #"Inserted Month Name"

That last step will convert January to "Jan" for you, just showing 3 letters instead of the full month name.

• In your data table, if "012018_6120.xlsx" is in the FileName field, use this in a new column:

  • Number.From(Text.Start([FileName],2))

  • Change that column type to an integer/whole number.
• Import both the date table and the data table with file names into your model.
• Mark the date table as a Dates Table, using the Date field.
• Releate the Month field in the date table to the Month field in your Data table. It would look like this:
  • 

For any visual or measures, use the Month field in the Dates table and it will automatically do any date intelligence correctly, and correctly pull related fields from your data table, which is Table1 in my example.