How to extract 30% of records from a table?

[joey]

Hi all,

I have a table with 860 rows. I need to extract 30% of the records from a table.

May I know how to do it in power query?

Thanks!

[jbwtp · Accepted solution]

= Table.FirstN(Source, Number.Round(Table.RowCount(Source)*0.3))

[AlexisOlson]

If you want to sample not just the first rows, then there are a variety of methods available. One example is to select the first three out of every 10 rows. You can do this by adding an index column (from 0) and then filtering like this:

= Table.SelectRows(#"Added Index", each Number.Mod([Index], 10) < 3 )

 This returns rows {0,1,2, 10,11,12, 20,21,22, 30,31,32,..., 870,871,872}.

[jbwtp · Accepted solution]

= Table.FirstN(Source, Number.Round(Table.RowCount(Source)*0.3))

[joeywong]

@jbwtp 

I need another help here..

The number of records to be extracted will change based on the sum of another tables/query. It will change dynamically. How does the replace the 0.3 to dynamic function integrated?

[joeywong942 · Accepted solution]

Thanks@jbwtp . It does work.

[Greg_Deckler]

@joey Add an Index column. Filter the Index column for <= 258

[joeywong]

@grey_Deckler

Thank you for your suggestion on filtering the Index column for <= 258. However, I need another help here.  

The number of records that I need to extract will change based on the sum of another table or query. How can I dynamically change the number 258?

[jbwtp]

Hi @joeywong,

This would be:

= Table.FirstN(Source, Number.Round( AnotherQueryName * 0.3 ))

Assuming that AnotherQueryName is a query retunring a desired number of rows to extract.

Cheers,

John

[jbwtp]

Hi @joeywong,

Sorry, addressing your earlier quesiton, which I missed.

= Table.FirstN(Source, Number.Round( AnotherQueryName * PercentOfRows ))

Where PercentOfRows is the variable which contains the share of rows you want to slice.

Cheers,

John

I think you can guess 🙂 that it would be something like this: