Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Pull 10% of Random Rows and Display in a Table

Hi - 

 

So, simply put - i have a SharePoint List with 50 rows of data.

This is connected as a Data Set into my PowerBI dashboard.

What i want to do is have another table that simply retrieves 10% of the available rows, at random.

 

So, 50 records = Displays 5 rows, randomised.

 

Refreshing the data then causes the 5 rows to change again and again. Not worried about repetition or repeats, as long as the probability of repeat is as low as reasonably practical.

Need Help

2 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Here's one way to do it in the query editor.  Just update with your site URL and list Id (or make a query to your list and then add the last two rows, updating the step references in the advanced editor).

    let
    Source = SharePoint.Tables("https://xyz.sharepoint.com/sites/Sandbox", [Implementation="2.0", ViewMode="All"]),
    ListData = Source{[Id="e552e9e0-3614-432d-a4bb-944e54f642c0"]}[Items],
    ListItemCount = Table.RowCount(ListData),
    #"Filtered Rows" = Table.SelectRows(ListData, each List.Contains(List.Transform(List.Random(Number.Round(ListItemCount*0.1, 0)), each Number.Round(ListItemCount*_, 0)), [ID]))
in
    #"Filtered Rows"

     

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Ok, i've open query editor within the pbix that i have already made and in the left hand side, clicked "new query" and in the editor bar at the top, copied/pasted your code and swapped out the URL and ListID correctly, hit the Tick button and its all red, hitting apply doesn't generate a table of data within query editor- what am i doing wrong?