Power Query Question on extracting data to create a date!

[scpournara]

In creating a Power Query by grabbing data from a Folder with .CSV files, I end up with the Source.Name with 8 characters that represent the Date of the file. Trying to extract and format those numbers as a date. I can create a Custom column from the selection and isolate the 8 characters but can not get them inot a date format. Any suggestions?

 

[jdellasala]

Starting with this:

This is the query to get out the date from the samples I had available:

let
    Source = Folder.Files("C:\Users\jdell\Dropbox\Excel\MyOnlineTrainingHub\excel_dashboard_dark_theme_db_dl\Data"),
    // Added to insure all extensions were lower case.
    LowercasedText = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    // Added to insure only .csv files were being handled. More could be done with the Name column to insure no stray files are included.
    FilteredRows = Table.SelectRows(LowercasedText, each [Extension] = ".csv"),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows,{"Name", "Content"}),
    // To prepare to get the last two characters of the Name column.
    ReplacedValue = Table.ReplaceValue(RemovedOtherColumns,".csv","",Replacer.ReplaceText,{"Name"}),
    InsertedFirstCharacters = Table.AddColumn(ReplacedValue, "Year", each Text.Start([Name], 4), type text),
    InsertedLastCharacters = Table.AddColumn(InsertedFirstCharacters, "Month", each Text.End([Name], 2), type text),
    RemovedColumns = Table.RemoveColumns(InsertedLastCharacters,{"Name"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"Year", "Month", "Content"}),
    // Auto Generated
    FilteredHiddenFiles1 = Table.SelectRows(ReorderedColumns, each [Attributes]?[Hidden]? <> true),
    // Auto Generated
    InvokeCustomFunction1 = Table.AddColumn(FilteredHiddenFiles1, "Transform File", each #"Transform File"([Content])),
    // Auto Generated BUT added "Year", "Month", manually to keep them.
    RemovedOtherColumns1 = Table.SelectColumns(InvokeCustomFunction1, {"Year", "Month", "Transform File"}),
    ExpandedTransformFile = Table.ExpandTableColumn(RemovedOtherColumns1, "Transform File", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"})
in
    ExpandedTransformFile

Got this:

Note that I had to manually stop PQ from removing the Year and Month column:
// Auto Generated BUT added "Year", "Month", manually to keep them.
RemovedOtherColumns1 = Table.SelectColumns(InvokeCustomFunction1, {"Year", "Month", "Transform File"}),
By default, PQ is going to generate a Parameter query, a "Sample File" (binary of one file), a Transform function, and a Transform Sample query, and then append all the files. The Transform Sample query is where you make changes you want to happen to all the files. Only change types when needed - they won't transfer over - do that as late as possible in the main query. I added comments to some of the steps' Properties
Note that if you try to use the above query, you will have to delete the step FilteredHiddenFiles1 and do the rest manually. Don't forget to add the date columns you add to the RemovedOtherColumns1 step.

Hope that answers the question.

 

[RoryA]

Depending on what version of PQ you have, once you have the 8 characters, you could use Date.FromText(yourtextfield, [Format="MMddyyyy", Culture="en-US"])