1
\$\begingroup\$

Background

Consider this trivial dataset:

x y
1 1001 A
2 1002 B
3 1003 C
4 1004 D
5 1005 E

For an R programmer, it is easy to subset this arbitrarily by position:

dataset <- tibble::tribble(
    ~x,   ~y,
    1001, "A",
    1002, "B",
    1003, "C",
    1004, "D",
    1005, "E"
)

dataset[c(4, 1, 3), ]
#         ^^^^^^^
#   Indexing starts at 1
# A tibble: 3 × 2
      x y    
  <dbl> <chr>
1  1004 D    
2  1001 A    
3  1003 C  

I want to do likewise in PowerQuery:

let
    dataset = Table.TransformColumnTypes(Table.FromRecords({
        [x = 1001, y = "A"],
        [x = 1002, y = "B"],
        [x = 1003, y = "C"],
        [x = 1004, y = "D"],
        [x = 1005, y = "E"]
    }), {
        {"x", type number},
        {"y", type text}
    }),
    
    subset = Foo(dataset, {3, 0, 2})
    //                     ^^^^^^^
    //               Indexing starts at 0
in
    subset

Unfortunately, PowerQuery can only do this for contiguous ranges, via Table.Range() and Table.RemoveRows() and so forth.

Solution

List_Subset()

This subsets a list by position, and it is a workhorse.

let List_Subset = (
    list as list,
    indices as list,
    optional default as nullable any
) as list =>
    let
        // Return an empty list if no positions are supplied...
        result = if List.IsEmpty(indices) then
            {}
            
        // ...and otherwise map the positions to their values, which default when out of bounds.
        else
            List.Transform(indices, each try list{_} otherwise default)
    in
        result
in
    List_Subset

Table_Subset()

This extends subsetting to the rows of a table.

let Table_Subset = (
    table as table,
    indices as list,
    optional default as nullable record
) as table =>
    let
        // Defaults.
        dfl = if default = null then
            []
        else
            default,
        
        // Preserve the names and types of the columns.
        hdr = Table.FirstN(table, 0),
        
        // Return the empty table if no positions are supplied...
        result = if List.IsEmpty(indices) then
            hdr
            
        // ...and otherwise select the records by position, and stack them under the header.
        else
            let
                rcd = List_Subset(Table.ToRecords(table), indices, dfl),
                sub = Table.InsertRows(hdr, 0, rcd)
            in
                sub
    in
        result
in
    Table_Subset

List_RandomBetween()

This is to List.Random() what Number.RandomBetween() is to Number.Random(), and it is a helper.

let List_RandomBetween = (
    count as number,
    bottom as number,
    top as number,
    optional seed as nullable number
) as list =>
    let
        // Generate randoms within the range [0, 1]...
        rnd = List.Random(count, seed),
        
        // ...then map them to the range [bottom, top].
        result = List.Transform(rnd, each _ * (top - bottom) + bottom)
    in
        result
in
    List_RandomBetween

List_RandomInt()

This generates a list of integers between bottom and top, with or without repetition, and it is a helper.

let List_RandomInt = (
    count as number,
    bottom as number,
    top as number,
    optional repeat as nullable logical,
    optional seed as nullable number
) as list =>
    let
        // Constants.
        INT_COL = "Integer",
        RNK_COL = "Rank",
        
        // Defaults.
        rep = if repeat = null then
            false
        else
            repeat,
        
        // Truncate the count to an integer.
        cnt = Number.RoundDown(count, 0),
        btm = Number.RoundDown(bottom, 0),
        tp = Number.RoundDown(top, 0),
        n = tp - btm + 1,
        
        // Return an empty list for a nonpositive count...
        result = if cnt <= 0 then
            {}
            
        // ...or a list of blanks for invalid bounds...
        else if btm > tp then
            let
                // Without repetition the length is restricted.
                lng = if rep then
                    cnt
                else
                    List.Min({cnt, n}),
                blnk = List.Repeat({null}, lng)
            in
                blnk
            
        // ...and otherwise sample the integers:
        else
            // Sample with repetition by truncating random decimals into integers...
            if rep then
                let
                    rnd = List_RandomBetween(cnt, btm, tp + 1, seed),
                    int = List.Transform(rnd, each Number.RoundDown(_, 0))
                in
                    int
                
            // ...or without repetition by taking the top integers of random rank.
            else
                let
                    rnk = List.Random(n, seed),
                    int = List.Numbers(btm, n, 1),
                    tbl = Table.FromColumns({int, rnk}, {INT_COL, RNK_COL}),
                    srt = Table.Sort(tbl, {RNK_COL, Order.Ascending}),
                    col = Table.Column(srt, INT_COL),
                    top = List.FirstN(col, cnt)
                in
                    top
    in
        result
in
    List_RandomInt

List_Sample()

This samples values from a list, with or without repetition, and it is a workhorse.

let List_Sample = (
    list as list,
    count as number,
    optional repeat as nullable logical,
    optional order as nullable logical,
    optional seed as nullable number
) as list =>
    let
        // Defaults.
        ord = if order = null then
            true
        else
            order,
        
        // Truncate the count to an integer.
        cnt = Number.RoundDown(count, 0),
        
        // Return an empty list for an empty input or nonpositive count...
        result = if List.IsEmpty(list) or cnt <= 0 then
            {}
            
        // ...and otherwise sample the values:
        else
            let
                // Record the length of the list...
                lng = List.Count(list),
                
                // ...and sample its positions.
                pos = List_RandomInt(cnt, 0, lng - 1, repeat, seed),
                
                // Optionally restore the positions to their original order...
                srt = if ord then
                    List.Sort(pos)
                else
                    pos,
                
                // ...before extracting their values.
                smp = List_Subset(list, srt)
            in
                smp
    in
        result
in
    List_Sample

Table_Sample()

This extends sampling to the rows of a table.

let Table_Sample = (
    table as table,
    count as number,
    optional repeat as nullable logical,
    optional order as nullable logical,
    optional seed as nullable number
) as table =>
    let
        // Randomly sample the row positions...
        lng = Table.RowCount(table),
        idx = List.Numbers(0, lng, 1),
        rnd = List_Sample(idx, count, repeat, order, seed),
        
        // ...and subset the table by those positions.
        result = Table_Subset(table, rnd)
    in
        result
in
    Table_Sample

Example

When applied to the original example, List_Subset() works as intended:

let
    dataset = Table.TransformColumnTypes(Table.FromRecords({
        [x = 1001, y = "A"],
        [x = 1002, y = "B"],
        [x = 1003, y = "C"],
        [x = 1004, y = "D"],
        [x = 1005, y = "E"]
    }), {
        {"x", type number},
        {"y", type text}
    }),
    
    subset = Table_Subset(dataset, {3, 0, 2})
    //                              ^^^^^^^
    //                        Indexing starts at 0
in
    subset
x y
1 1004 D
2 1001 A
3 1003 C

Features

Future features might include

  • Letting Table_Subset() extract data by row and column simultaneously, where one may specify columns by name or by position: Table_Subset(dataset, {3, 0, 2}, {"x", 1}). This reflects the simultaneous subsetting permitted in R.
  • Adding a skip argument to *_Subset(), which simply omits the results from indices that are out of bounds.
  • Extending *_Subset() to records and other data structures.
  • Implementing custom errors that detail any misuse: indices out of bounds, nonexistent records, contradictory bounds (bottom and top), and so forth.
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.