PowerQuery list functions cheat sheet

list-cheat-sheet.md

List functions

Addition

List.Sum({1, 2, 3}) ⇒ 6

Averages

List.Average({3, 4, 6}) ⇒ 4.333333333333333
List.Mode({"A", 1, 2, 3, 3, 4, 5}) ⇒ 3
List.Modes({"A", 1, 2, 3, 3, 4, 5, 5}) ⇒ {3,5}
List.StandardDeviation({1..5}) ⇒ 1.5811388300841898

Generators

List.Generate(() => 10, each _ > 0, each _ - 1) ⇒ {10, 9, 8, 7, 6, 5, 4, 3, 2, 1}
List.Random(3, 2) ⇒ {0.883002, 0.245344, 0.723212}
List.Numbers(1, 10, 2) ⇒ {1,3,5,7,9,11,13,15,17,19}
List.Durations(#duration(0, 1, 0, 0), 5, #duration(0, 1, 0, 0)) ⇒ {
        #duration(0, 1, 0, 0),
        #duration(0, 2, 0, 0),
        #duration(0, 3, 0, 0),
        #duration(0, 4, 0, 0),
        #duration(0, 5, 0, 0)
    }
List.DateTimeZones(#datetimezone(2011, 12, 31, 23, 55, 0, -8, 0), 10, #duration(0, 0, 1, 0)) ⇒ {
        #datetimezone(2011, 12, 31, 23, 55, 0, -8, 0),
        #datetimezone(2011, 12, 31, 23, 56, 0, -8, 0),
        #datetimezone(2011, 12, 31, 23, 57, 0, -8, 0),
        #datetimezone(2011, 12, 31, 23, 58, 0, -8, 0),
        #datetimezone(2011, 12, 31, 23, 59, 0, -8, 0),
        #datetimezone(2012, 1, 1, 0, 0, 0, -8, 0),
        #datetimezone(2012, 1, 1, 0, 1, 0, -8, 0),
        #datetimezone(2012, 1, 1, 0, 2, 0, -8, 0),
        #datetimezone(2012, 1, 1, 0, 3, 0, -8, 0),
        #datetimezone(2012, 1, 1, 0, 4, 0, -8, 0)
    }
List.Times(#time(12, 0, 0), 4, #duration(0, 1, 0, 0)) ⇒ {
        #time(12, 0, 0),
        #time(13, 0, 0),
        #time(14, 0, 0),
        #time(15, 0, 0)
    }
List.Dates(#date(2011, 12, 31), 5, #duration(1, 0, 0, 0)) ⇒ {
        #date(2011, 12, 31),
        #date(2012, 1, 1),
        #date(2012, 1, 2),
        #date(2012, 1, 3),
        #date(2012, 1, 4)
    }
List.DateTimes(#datetime(2011, 12, 31, 23, 55, 0), 10, #duration(0, 0, 1, 0)) ⇒ {
        #datetime(2011, 12, 31, 23, 55, 0),
        #datetime(2011, 12, 31, 23, 56, 0),
        #datetime(2011, 12, 31, 23, 57, 0),
        #datetime(2011, 12, 31, 23, 58, 0),
        #datetime(2011, 12, 31, 23, 59, 0),
        #datetime(2012, 1, 1, 0, 0, 0),
        #datetime(2012, 1, 1, 0, 1, 0),
        #datetime(2012, 1, 1, 0, 2, 0),
        #datetime(2012, 1, 1, 0, 3, 0),
        #datetime(2012, 1, 1, 0, 4, 0)
    }

Information

List.NonNullCount({1, 2, 3,null}) ⇒ 3
List.IsEmpty({1, 2}) ⇒ false
List.Count({1, 2, 3}) ⇒ 3

Membership

List.ContainsAll({1, 2, 3, 4, 5}, {3, 4}) ⇒ true
List.ContainsAny({1, 2, 3, 4, 5}, {6, 7}) ⇒ false
List.AnyTrue({2 = 0, false, 2 < 0}) ⇒ false
List.Contains({1, 2, 3, 4, 5}, 6) ⇒ true
List.AllTrue({true, true, 2 > 0}) ⇒ true
List.PositionOf({1, 2, 3}, 3) ⇒ 2
List.PositionOfAny({1, 2, 3}, {2, 3}) ⇒ 1

Numerics

List.Product({1, 2, 3, 3, 4, 5, 5}) ⇒ 1800
List.Covariance({1, 2, 3}, {1, 2, 3}) ⇒ 0.66666666666666607

Ordering

List.Percentile({5, 3, 1, 7, 9}, 0.25) ⇒ 3
List.MinN({3, 4, 5, -1, 7, 8, 2}, 5) ⇒ {-1, 2, 3, 4, 5}
List.Min({1, 4, 7, 3, -2, 5}) ⇒ -2
List.MaxN({3, 4, 5, -1, 7, 8, 2}, 5) ⇒ {3, 4, 5, 7, 8}
List.Max({1, 4, 7, 3, -2, 5}) ⇒ 7
List.Median({5, 3, 1, 7, 9}) ⇒ 5
List.Sort({2, 3, 1}) ⇒ {1, 2, 3}

Selection

List.MatchesAny({9, 10, 11}, each _  > 10) ⇒ true
List.MatchesAll({11, 12, 13}, each _  > 10) ⇒ true
List.Alternate({1..10}, 1, 1, 1) ⇒ {1, 3, 5, 7, 9}
List.InsertRange({1, 2, 5}, 2, {3, 4}) ⇒ {1, 2, 3, 4, 5}
List.Positions({1, 2, 3, 4, null, 5}) ⇒ {0, 1, 2, 3, 4, 5}
List.Skip({1, 2, 3, 4, 5}, 3) ⇒ {4, 5}
List.Last({1, 2, 3}) ⇒ 3
List.Select({1, -3, 4, 9, -2}, each _ > 0) ⇒ {1, 4, 9}
List.SingleOrDefault({}, -1) ⇒ -1
List.Single({1}) ⇒ 1
List.Range({1..10}, 6, 2) ⇒ {7, 8}
List.FindText({"a", "b", "ab"}, "a") ⇒ {"a", "ab"}
List.IsDistinct({1, 2, 3, 3}) ⇒ false
List.Distinct({1, 1, 2, 3, 3, 3}) ⇒ {1, 2, 3}
List.First( {1, 2, 3}) ⇒ 1
List.FirstN({3, 4, 5, -1, 7, 8, 2}, each _ > 0) ⇒ {3, 4, 5}
List.LastN( {3, 4, 5, -1, 7, 8, 2}, each _ > 0) ⇒ {7, 8, 2}

Set operations

List.Intersect({{1..5}, {2..6}, {3..7}}) ⇒ {3, 4, 5}
List.Difference({1, 2, 3, 4, 5}, {4, 5, 3}) ⇒ {1, 2}
List.Union({{1..5}, {2..6}, {3..7}}) ⇒ {1, 2, 3, 4, 5, 6, 7}

Transformation

List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) ⇒ 15
List.Transform({1, 2}, each _ + 1) ⇒ {2, 3}
List.Zip({{1, 2}, {3, 4}}) ⇒ {{1, 3}, {2, 4}}
List.Combine({{1, 2}, {3, 4}}) ⇒ {1, 2, 3, 4}
List.Split({1..10},3) ⇒ {{1, 2, 3}, {4, 5, 6}, {7, 8, 9}, {10}}
List.Repeat({1, 2}, 3) ⇒ {1, 2, 1, 2, 1, 2}
List.Reverse({1..10}) ⇒ {10, 9, 8, 7, 6, 5, 4, 3, 2, 1}
List.ReplaceValue({"a", "B", "a", "a"}, "a", "A", Replacer.ReplaceText) ⇒ {"A", "B", "A", "A"}
List.RemoveItems({1, 2, 3, 4, 2, 5, 5}, {2, 4, 6}) ⇒ {1, 3, 5, 5}
List.RemoveLastN({5, 4, 2, 6, 4}, each _ > 3) ⇒ {5, 4, 2}
List.RemoveFirstN({5, 4, 2, 6, 1}, each _ > 3) ⇒ {2, 6, 1}
List.RemoveMatchingItems({1, 2, 3, 4, 5, 5}, {1, 5}) ⇒ {2, 3, 4}
List.RemoveRange({1, 2, 3, 4, -6, -2, -1, 5}, 4, 3) ⇒ {1, 2, 3, 4, 5}
List.ReplaceRange({1, 2, 7, 8, 9, 5}, 2, 3, {3, 4}) ⇒ {1, 2, 3, 4, 5}
List.RemoveNulls({1, 2, 3, null, 4, 5, null, 6}) ⇒ {1, 2, 3, 4, 5, 6}
List.ReplaceMatchingItems({1, 2, 3, 4, 5}, {{5, -5}, {1, -1}}) ⇒ {-1, 2, 3, 4, -5}