Thank you SO much for this video! The function works perfectly! From this I was able to I adapt it for a project where I had to: 1) group the data by a specific category using Group By and 2) take a sample of each table based on a fixed percentage (ex. 10%). I changed the initial "SampleSize" parameter to "PercentageSample", but still using a whole number as the expected input. Then I made a "SampleSize" variable/step before the "Source" step that calculated the SampleSize based on the RowCount of the table: SampleSize = Number.Round((PercentageSample/100) * Table.RowCount(AnyTable)). The rest of the function is the same as your video. I expand the nested tables and I'm all set. Thank you!
Thanks for the video, an interesting problem to solve and a good way of acheiving the result. I found that adding a List.Buffer statement to the code marginally decreased the amount of time taken to refresh the tables.
It looks like we don't need to subtract the number and can do it like this: = List.Transform(Source, each Number.RoundDown(Number.RandomBetween(0,80)))
Thank you so much for this interesting video. It's very informative. I have one real time business use case. Could you please help me. I have a column name as "Team Name". Think as "Band" column in your staff table. The need is we need to extract the sample based on Value in this column. Example: Where team is "A" extract 10 records and where Team is "B" extract 8 records. Means to say - I have created a business rule table with this Team information. One column is which team and other column is how many records to extract. I want to load this into power query and extract the data based on those records define. Band Sample Size A 10 B 8 C 4 D 9 E 7 Some how, I am thinking the logic - how to build this information in invoke function where it will select the required numbers accordingly. I tried a lot, but unable to achieve it. Could you please help me.
Thank you SO much for this video! The function works perfectly! From this I was able to I adapt it for a project where I had to: 1) group the data by a specific category using Group By and 2) take a sample of each table based on a fixed percentage (ex. 10%).
I changed the initial "SampleSize" parameter to "PercentageSample", but still using a whole number as the expected input. Then I made a "SampleSize" variable/step before the "Source" step that calculated the SampleSize based on the RowCount of the table: SampleSize = Number.Round((PercentageSample/100) * Table.RowCount(AnyTable)). The rest of the function is the same as your video. I expand the nested tables and I'm all set. Thank you!
Brilliant, well done 👍
Thanks for this video. You taught me how to make a versatile tool!
Happy that you found it helpful
Nice one! Please, I need the function.
thank you, what function do you need? i think all explained in the video, and you can download the working file from the link in the description
Thanks for the video, an interesting problem to solve and a good way of acheiving the result. I found that adding a List.Buffer statement to the code marginally decreased the amount of time taken to refresh the tables.
Your are welcome Roy, I will try it, thank you so much for your tip 👍
Plz suggest how to same for a cluster, where we can get ywo samples from each cluster
Very nice tutorial. Thank you.
Glad it was helpful!
Many thanks 🙏 Sir
Most welcome 🙏
It looks like we don't need to subtract the number and can do it like this:
= List.Transform(Source, each Number.RoundDown(Number.RandomBetween(0,80)))
Great, thank you for the tip 👍
That would also address the issue that rounding up would never allow record 0 to be chosen.
Thank you so much for this interesting video. It's very informative. I have one real time business use case. Could you please help me. I have a column name as "Team Name". Think as "Band" column in your staff table. The need is we need to extract the sample based on Value in this column. Example: Where team is "A" extract 10 records and where Team is "B" extract 8 records. Means to say - I have created a business rule table with this Team information. One column is which team and other column is how many records to extract. I want to load this into power query and extract the data based on those records define.
Band Sample Size
A 10
B 8
C 4
D 9
E 7
Some how, I am thinking the logic - how to build this information in invoke function where it will select the required numbers accordingly. I tried a lot, but unable to achieve it. Could you please help me.