m-dekorte

let
    extractCharactersByCase = (input as nullable text, optional caseType as text) as text =>
        Text.Combine(
            List.Select(
                Text.ToList(input ?? ""),
                (s) =>
                    if Text.Proper(caseType ?? "Upper") = "Upper"
                    then not (Text.Lower(s) = s)
                    else not (Text.Upper(s) = s)
            )

m-dekorte

var dateTableName = "Dates"; // Name of your date table
var dateKeyColName = "Date"; // Name of the date key column in the table
var colOperations = new List<Tuple<string, string, bool>>(){
    Tuple.Create("Quarter & Year", "QuarternYear", true),
    Tuple.Create("Month & Year", "MonthnYear", true),
    Tuple.Create("Month Name", "MonthOfYear", true),
    Tuple.Create("Month Short", "MonthOfYear", true),
    Tuple.Create("Month Initial", "MonthOfYear", true),
    Tuple.Create("Week & Year", "WeeknYear", true),
    Tuple.Create("Day of Week Name", "DayOfWeek", true),

m-dekorte

let
    fnDateTable = ( StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number, optional AddRelativeNetWorkdays as logical ) as table =>
    let
        FYStartMonth = List.Select({1..12}, each _ = FYStartMonthNum){0}? ?? 1,
        WDStart = List.Select({0..1}, each _ = WDStartNum){0}? ?? 0,
        CurrentDate = Date.From(DateTimeZone.FixedUtcNow()),
        DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
        weekStart = Day.Monday,
        HolidaysProvided = Holidays <> null,

m-dekorte

let
    /* Function to convert an input value like a date, datetime, datetimezone, or text to a target type */
    convertTo = let
        fxConvertTo = (Value as any, ReturnType as text, optional Culture as text) as any =>
            let
                TypeSelector = List.PositionOf(AvailableTypes, ReturnType, 0, Comparer.OrdinalIgnoreCase),
                BaseDate = DateTimeZone.From(Value, (Culture ?? Culture.Current)),
                TypeValue = {Date.From(BaseDate), DateTime.From(BaseDate), BaseDate}{TypeSelector}
            in
                TypeValue,

m-dekorte

let
    colTypes = [
        #"any" = Any.Type,
        #"date" = Date.Type,
        #"time" = Time.Type,
        #"datetime" = DateTime.Type,
        #"logical" = Logical.Type,
        #"number" = Number.Type,
        #"currency" = Currency.Type,
        #"integer" = Int64.Type,

m-dekorte

let
    /* This was an exercise just for fun. Who knows, it might have a real-world application... */
    /* Generate every possible substring combination from a string */
    getAllSubstrings = (s as text, optional makeDistinct as logical, optional ignoreCase as logical) as list =>
        let
            c = if ignoreCase ?? false then Comparer.OrdinalIgnoreCase else Comparer.Ordinal,
            n = Text.Length(s),
            sub = List.TransformMany(
                {0..n-1},
                each {_+1..n},

m-dekorte

let
    fxErrorReport = (myTable as table, optional keyCols as list, optional scanCols as list ) as record =>
    let
        colNames = Table.ColumnNames( myTable ),
        validKeys = List.Intersect( { colNames, keyCols ?? {} } ),
        hasKeys = List.Count( validKeys ) >0,
        selCols = List.Intersect( { colNames, scanCols ?? {} } ),
        nTable = Table.SelectColumns( myTable, if List.Count( selCols ) >0 then validKeys & selCols else colNames ),
        reservedNames = { "Name", "Errors", "Reason", "Message", "Detail", "rowIndex", "nCol" },
        nameViolations = List.Intersect( { colNames, reservedNames } ),

m-dekorte

(t as table) as table => 
    Table.Profile(t,   
        {{ 
            "ErrorCount", 
            each true, 
            each Table.RowCount(Table.SelectRowsWithErrors(Table.FromColumns({_}))) 
        }}
    )

m-dekorte

(t as table, optional replacementValue as any, optional scanCols as list) as table =>
let
    colNames = Table.ColumnNames(t),
    selCols = List.Intersect({colNames, scanCols ?? {}}),
    replErrors = Table.ReplaceErrorValues(t, 
        List.Transform(
            if List.IsEmpty(selCols) then colNames else selCols, 
            each {_, replacementValue}
        )
    )

m-dekorte

trimChar = LAMBDA(str, [char], [mode],
    LET(
        charToTrim, IF(ISOMITTED(char), " ", LEFT(char, 1)),
        trimMode, IF(OR(mode = 1, mode = 2, mode = 3), mode, 1),
        // Trim functions
        trimStart, LAMBDA(s,
            REDUCE(
                s,
                SEQUENCE(LEN(s)),
                LAMBDA(a, _,