fnRandomNumber.pq

(t as table, seed as number) =>
let
    // Step 1: Add an index column to the input table to preserve row order and enable alignment
    Source = Table.AddIndexColumn(t, "__Index", 0, 1, Int64.Type),

    // Step 2: Generate a reproducible list of random numbers using the provided seed
    // This ensures consistent output across refreshes or deployments
    Random_List = List.Random(Table.RowCount(t), seed),

    // Step 3: Convert the random list into a table and add an index column for joining
    Add_Index_To_Random_List = Table.AddIndexColumn(
        Table.FromList(
            Random_List,
            Splitter.SplitByNothing(),
            type table [Random = Number.Type],
            null,
            ExtraValues.Error
        ),
        "__Index",
        0,
        1
    ),

    // Step 4: Join the original table with the random number table using the index
    // This aligns each original row with its corresponding random value
    Join_Tables = Table.Join(
        Source,
        "__Index",
        Add_Index_To_Random_List,
        "__Index",
        JoinKind.Inner
    ),

    // Step 5: Remove the temporary index column to clean up the final output
    Remove_Index = Table.RemoveColumns(Join_Tables, "__Index")
in
    Remove_Index