CODEWITHSUNDEEP
excelpowerquery
RedArrogantKnight
RedArrogantKnight

Reputation: 101

Random sample in PowerQuery

I'm new to PowerQuery in Excel and I'm trying to get a random sample from a table, but nothing I do seems to be working.

I have a table with a few hundred entries and I want a sample of fifteen. (Non-repeating.)

I've Googled this problem extensively and none of the examples work for me, but I honestly don't know why. Is there anyone who can help me understand how to accomplish this?

Thank you very much!

Upvotes: 0

Views: 818

Answers (1)

Olly
Olly

Reputation: 7891

Try something like this - replace Source as appropriate:

= Table.RemoveColumns(Table.FirstN(Table.Sort(Table.Buffer(Table.AddColumn(Source, "Random", each Number.Random())), {"Random", Order.Ascending}),15),{"Random"})

Or if you prefer to see it step by step:

let
    Source = MySourceTable,
    #"Added Random" = Table.AddColumn(Source, "Random", each Number.Random()),
    #"Buffered Random Values" = Table.Buffer(#"Added Random"),
    #"Sorted Rows by Random" = Table.Sort(#"Buffered Random Values",{{"Random", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows by Random",15),
    #"Removed Random Column" = Table.RemoveColumns(#"Kept First Rows",{"Random"})
in
    #"Removed Random Column"

Upvotes: 1

Related Questions