Selecting Sample based on volume

[Anonymous]

Hi Power Bi Community!

What I need to is select a sample (this could be a new table). I need to select 50% of any category having more than 10 records  any records  || if less than 10 and 5 or more records then to select 5 || if below 5 return all records.

For Example: the table returned (output) from the table below (input), should be 5 records with A, 5 records with B and 3 records with C. 

I've tried to use the sample formula but I can only apply 1 number to the sample size (e.g 0.5 to represent the 50%) but i cannot select sample based on the count of record. any help will be much appreciated, thank you in advance.

Category	ID	Value
A	1	25
A	2	36
A	3	15
A	4	12
A	5	23
A	6	12
A	7	16
A	8	12
A	9	45
A	10	12
B	11	71
B	12	15
B	13	14
B	14	55
B	15	26
B	16	43
C	17	21
C	18	11
C	19	19

[Anonymous]

@mahoneypat you are awesome! thank you so much

[mahoneypat · Accepted solution]

Here is one way to do that in the query editor.  If you need both, you could reference your current query and do this.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  This keeps the latest 5 rows (but you could remove the sort step to keep the first).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY67DcAgDER3cU0RzL9MMgZi/zXCYYkLhRFPB+fXu9zixM/RJMMZ6pyQNwa8YBqBujHhQ9iYz7QAWVXPtKGOzf5i/OAKr+LJSpPFyyySoZZ++XLLZMhFc33BsFNPrrZ0c7NjjA8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category"}, {{"AllRows", each _, type table [Category=nullable text, ID=nullable number, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "First5", each Table.FirstN([AllRows], 5)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded First5" = Table.ExpandTableColumn(#"Removed Columns", "First5", {"ID", "Value"}, {"ID", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded First5",{{"ID", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type1"

Regards,

Pat

[Anonymous]

Hi @mahoneypat ,Thank you for coming back with the code, it seems like it's pulling off the last five on each category. this is useful but I need somthing that takes a % sample as first parameter for high counts, then 5 for count >4  and <10 anything followed by all within that category. 

[mahoneypat]

Here you go.  This version does it dynamically.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY67DcAgDER3cU0RzL9MMgZi/zXCYYkLhRFPB+fXu9zixM/RJMMZ6pyQNwa8YBqBujHhQ9iYz7QAWVXPtKGOzf5i/OAKr+LJSpPFyyySoZZ++XLLZMhFc33BsFNPrrZ0c7NjjA8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category"}, {{"AllRows", each _, type table [Category=nullable text, ID=nullable number, Value=nullable number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Rows To Keep", each let rowcount = Table.RowCount([AllRows]) in if rowcount > 10 then 0.5 * rowcount else 5),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "First5", each Table.FirstN([AllRows], [Rows To Keep])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded First5" = Table.ExpandTableColumn(#"Removed Columns", "First5", {"ID", "Value"}, {"ID", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded First5",{{"ID", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type1"

Regards,

Pat