Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
= Table.FirstN(Source, Number.Round(Table.RowCount(Source)*0.3))
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}.
= Table.FirstN(Source, Number.Round(Table.RowCount(Source)*0.3))
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?
@joey Add an Index column. Filter the Index column for <= 258
@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?
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
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.