Calculate Expired Date with T in Date Format

[Vgabond]

Hi MrExcel Guru

Seeking for your assistance as I have more than 100 csv files in a folder that I need to do the following :
1. Count row that have Expired Date. Date format is YYYY-MM-DDTHH:MM:SS. Then write the count of rows ( expired date) in a new sheet : Summary Expired Date
2. Write the file name in sheet: Summary Expired Date

Any assistance is appreciated

 

[alex78]

Yes. Any date in the column O that is less than the day the query is run.

Hi @Vgabond,

try this:

let
    Data = {"C:\PQ", "O"}, // to be updated with folder location and column letter with dates
    Process = Table.TransformColumns(
                    Table.SelectColumns(Folder.Files(Data{0}), {"Name","Content"}), 
                    {"Content", 
                        (x)=> let y = Table.ToColumns(Csv.Document(x,[Encoding=1200])){List.PositionOf({"A".."Z"}, Data{1})} 
                        in List.Count(
                            List.Select(
                                List.Transform(y , (x)=> Date.FromText(Text.BeforeDelimiter(x,"T"))),
                            each _ < DateTime.Date(DateTime.LocalNow())))
                    }
                        ),
    #"Rename Col" = Table.RenameColumns(Process, List.Zip({Table.ColumnNames(Process), {"Csv Name", "Expired dates count"}}))
in
    #"Rename Col"

Regards,