findMinMaxDates M function | Find the Min and Max dates across multiple queries and/ or columns, for example to set up a dynamic date table. This method leverages the Table.Profile function and handles known limitations such as null and/ or inconsistent/ missing column types.

findMinMaxDates.pq

let
    fxFindMinMaxDates = (tableNames as list, optional dateColsID as text, optional excludeDateColNames as list) as record =>
    let
        selectedTables = Record.SelectFields(
            #sections[Section1],
            tableNames,
            MissingField.Ignore
        ),
        rawData = Table.RemoveColumns(
            Table.Combine(Record.ToList(selectedTables)),
            (excludeDateColNames ?? {}),
            MissingField.Ignore
        ),
        dateCols = List.Select(
            Table.ColumnNames(rawData),
            each Text.Contains(_, (dateColsID ?? "date"), Comparer.OrdinalIgnoreCase)
        ),
        profileResult = if Record.FieldCount(selectedTables) < 1
            then [
                Min = null,
                Max = null,
                #"Table(s) found" = false,
                #"Column(s) found" = not List.IsEmpty(dateCols)
            ]
            else let
                result = Table.Profile(
                    Table.TransformColumns(
                        Table.SelectColumns(rawData, dateCols),
                        List.Transform(dateCols, each {_, each Date.From(DateTime.From(_)), type date})
                    ),
                    {
                        {"First", each true, each List.Min(List.RemoveMatchingItems(_, {"", null}))},
                        {"Last", each true, each List.Max(List.RemoveMatchingItems(_, {"", null}))}
                    }
                )[[First], [Last]],
                toRecord = [
                    Min = List.Min(result[First]),
                    Max = List.Max(result[Last]),
                    #"Table(s) found" = true,
                    #"Column(s) found" = not List.IsEmpty(dateCols)
                ]
            in
                toRecord
    in
        profileResult,
    TableNamesParameter = type list meta
        [
            Documentation.FieldCaption = "tableNames",
            Documentation.FieldDescription = "A list of (table) query names to include."
        ],
    DateColsIDParameter = type text meta
        [
            Documentation.FieldCaption = "dateColsID",
            Documentation.FieldDescription = "A substring to identify date-columns within the tables (e.g. ""date"" to match ""OrderDate"").",
            Documentation.SampleValues = {"""date"""}
        ],
    ExcludeDateColNamesParameter = type list meta
        [
            Documentation.FieldCaption = "excludeDateColNames",
            Documentation.FieldDescription = "A list of column names to exclude from the tables (e.g. ""Date of Birth"")."
        ],
    addParameterTypes = type function (
        tableNames as TableNamesParameter,
        optional dateColsID as DateColsIDParameter,
        optional excludeDateColNames as ExcludeDateColNamesParameter
    ) as record,
    fxWithDocumentation = Value.ReplaceType(fxFindMinMaxDates, addParameterTypes),
    
    /* 
        Each 'Sample' step must be copied into a separate query within the file, 
        with the query name matching the step name. 
        Otherwise, 'InvokedFunction' will return the Exception record.
    */
    Sample1 = #table(
        {"Name", "Onboarding Date", "Offboarding Date", "Date of Birth"},
        {
            {"Alice Smith", #date(2020, 1, 15), null, #date(1980, 5, 10)},
            {"Bob Johnson", #date(2020, 6, 20), #date(2021, 1, 25), #date(1975, 11, 3)},
            {"Carol Williams", #date(2021, 3, 5), null, #date(1990, 7, 22)},
            {"David Brown", #date(2021, 7, 10), #date(2022, 3, 15), #date(1965, 2, 14)},
            {"Emily Jones", #date(2022, 2, 28), null, #date(1995, 12, 1)},
            {"Frank Miller", #date(2022, 5, 16), #date(2023, 1, 10), #date(1988, 9, 30)},
            {"Grace Davis", #date(2023, 1, 20), null, #date(1970, 4, 25)},
            {"Henry Wilson", #date(2023, 8, 1), #date(2024, 8, 15), #date(1960, 1, 17)},
            {"Irene Taylor", #date(2024, 2, 14), null, #date(1992, 3, 15)},
            {"Jack Anderson", #date(2024, 7, 30), #date(2025, 2, 28), #date(1982, 10, 5)},
            {"Karen Thomas", #date(2020, 11, 5), #date(2021, 7, 20), #date(1968, 6, 30)},
            {"Liam White", #date(2021, 12, 12), null, #date(1998, 2, 18)},
            {"Mia Harris", #date(2022, 9, 9), #date(2023, 4, 15), #date(1999, 9, 1)},
            {"Noah Martin", #date(2023, 4, 1), null, #date(1978, 8, 12)},
            {"Olivia Thompson", #date(2024, 11, 11), #date(2025, 6, 20), #date(1945, 11, 23)}
        }
    ),
    Sample2 = #table(
        {"Name", "Onboarding Date", "Offboarding Date", "Date of Birth"},
        {
            {"Peter Clark", #date(2020, 2, 10), #date(2021, 3, 15), #date(1972, 4, 12)},
            {"Quinn Lewis", #date(2020, 12, 1), null, #date(1981, 7, 19)},
            {"Rachel Walker", #date(2021, 5, 25), #date(2022, 1, 1), #date(1960, 9, 30)},
            {"Steven Hall", #date(2021, 11, 11), null, #date(1995, 3, 5)},
            {"Tina Allen", #date(2022, 3, 14), #date(2022, 12, 20), #date(1987, 6, 25)},
            {"Ulysses Young", #date(2022, 7, 7), null, #date(1955, 1, 2)},
            {"Victoria Hernandez", #date(2023, 2, 28), #date(2023, 10, 31), #date(1979, 12, 11)},
            {"William King", #date(2023, 9, 15), null, #date(1992, 10, 22)},
            {"Xavier Wright", #date(2024, 1, 1), #date(2024, 8, 5), #date(1944, 5, 17)},
            {"Yvonne Lopez", #date(2024, 6, 18), null, #date(1983, 2, 9)},
            {"Zachary Hill", #date(2024, 12, 24), #date(2025, 7, 30), #date(1971, 11, 2)},
            {"Anna Scott", #date(2021, 1, 5), null, #date(1990, 8, 16)},
            {"Benjamin Adams", #date(2022, 10, 10), #date(2023, 5, 20), #date(1962, 3, 29)},
            {"Chloe Baker", #date(2023, 12, 1), null, #date(1997, 1, 15)},
            {"Dylan Carter", #date(2025, 1, 10), null, #date(1949, 6, 8)}
        }
    ),
    Sample3 = null,
    Sample4 = #table(
        {"Name", "Onboarding Date", "Offboarding Date", "Date of Birth"},
        {
            {"Ethan Murphy", #date(2020, 3, 20), #date(2021, 2, 25), #date(1976, 12, 30)},
            {"Fiona Rivera", #date(2020, 8, 8), null, #date(1984, 11, 11)},
            {"George Cox", #date(2021, 4, 14), null, #date(1969, 9, 22)},
            {"Hannah Rogers", #date(2021, 9, 27), #date(2022, 5, 29), #date(1950, 6, 5)},
            {"Ian Peterson", #date(2022, 6, 1), null, #date(1993, 1, 7)},
            {"Julia Cook", #date(2022, 11, 19), #date(2023, 6, 25), #date(1988, 4, 16)},
            {"Kyle Morgan", #date(2023, 3, 3), #date(2024, 1, 10), #date(1973, 2, 2)},
            {"Laura Bell", #date(2023, 7, 21), null, #date(1996, 7, 31)},
            {"Michael Bailey", #date(2024, 2, 29), #date(2024, 9, 15), #date(1965, 10, 10)},
            {"Natalie Reed", #date(2024, 5, 18), null, #date(1952, 12, 20)},
            {"Oscar Flores", #date(2024, 10, 10), #date(2025, 4, 15), #date(1991, 3, 3)},
            {"Penelope Price", #date(2021, 2, 14), null, #date(1999, 7, 7)},
            {"Quentin Howard", #date(2022, 8, 25), #date(2023, 3, 30), #date(1943, 1, 1)},
            {"Rebecca Shaw", #date(2023, 11, 30), null, #date(1994, 9, 14)},
            {"Samuel Gordon", #date(2025, 6, 1), null, #date(1986, 5, 2)}
        }
    ),
    /* When queries are NOT separated this 'InvokedFunction' will yield the Exception record. 
        [
            Exception = [Min = null, Max = null, #"Table(s) found" = false, #"Column(s) found" = false],
            Outcome = [Min = #date(2020,1,15), Max = #date(2025, 7, 30), #"Table(s) found" = true, #"Column(s) found" = true]
        ]
    */
    InvokedFunction = fxFindMinMaxDates({"Sample1", "Sample2", "Sample4"}, "date", {"Date of Birth"})
in
    InvokedFunction