Brian Julius Solurion ro Omid's Power Query Challenge No.183

"C:\Users\brjul\OneDrive\Documents\Power BI Coding\Enterprise DNA Files\Power Query Challenges\Omid Power Query Challenges No.183.pbix"

I had Deepseek R1 write the code for this solution, based on the following prompt:

Write M code that calls an R Script that uses a regex via the Stringer package to extract the dates in standard dd/mm/yyyy format from the left column.  In the source column the year is specified first, then the month, then the day.  Ignore any time values

gistfile1.txt

let
    Source = Excel.Workbook(File.Contents("C:\Users\brjul\Downloads\CH-183 Match the Dates.xlsx"), null, true),
    Data = Source{[Item="Data",Kind="Table"]}[Data],
    InputTable = Table.SelectColumns(Data,{"Date"}),
    RScript = R.Execute(
        "library(stringr)
        month_to_num <- function(month) {
            month <- tolower(month)
            if (str_detect(month, '^\\d+$')) {
                return(as.integer(month))
            }
            months <- c('jan', 'feb', 'mar', 'apr', 'may', 'jun', 
                        'jul', 'aug', 'sep', 'oct', 'nov', 'dec')
            full_months <- c('january', 'february', 'march', 'april', 'may', 'june',
                             'july', 'august', 'september', 'october', 'november', 'december')
            idx <- match(month, c(months, full_months))
            if (is.na(idx)) {
                return(NA)
            } else {
                return((idx - 1) %% 12 + 1)
            }
        }
        process_dates <- function(date_strings) {
            result <- c()
            for (date_str in date_strings) {
                date_part <- strsplit(date_str, '\\s+')[[1]][1]
                pattern <- '^([0-9]{2,4})[-/]([0-9A-Za-z]+)[-/]([0-9]{1,2})$'
                if (str_detect(date_part, pattern)) {
                    matches <- str_match(date_part, pattern)
                    year <- matches[2]
                    month <- matches[3]
                    day <- matches[4]
                    if (nchar(year) == 2) {
                        year <- paste0('20', year)
                    }
                    month_num <- month_to_num(month)
                    if (is.na(month_num)) next
                    day <- sprintf('%02d', as.integer(day))
                    month_num <- sprintf('%02d', month_num)
                    formatted_date <- paste(day, month_num, year, sep='/')
                    result <- c(result, formatted_date)
                }
            }
            unique(result)
        }
        output_dates <- process_dates(InputDataSet$Date)
        OutputDataSet <- data.frame(Date = output_dates)", 
        [InputDataSet=InputTable]
    ),
    OutputTable = RScript{[Name="OutputDataSet"]}[Value]
in
    OutputTable