# 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.