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...
Need Help
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?