Power Query/DAX Randomizer

[Savvyk]

Hello,
I’m fairly new to power query, but I’ve been tasked with a project that I can’t seem to find a good solution for.

Basically, I have a data set that has a column with an employee name and a column with a sales ID (a unique number). I need to create a tool that will generate a table from this data that populates a random number of sales ids for every employee listed. (Or worst case, all sales ids per employee, but in a random order, however the preference given to me was 2-3 sales per employee)

I initially created a query with a connection to this data set (so multiple people can create a copy of the file and have access to the refreshed information) Then I made random column and a index column (which a buffer step to prevent duplicate random numbers)

The problem is that when I did this, it either shows only one sale per employee, or it shows all sales from that employee in the same order every time. The all sales thing wouldn’t be an issue, but I need it to work in a way that when the user refreshes the sales ID order refreshes, even if the data set is the same (mainly just the order of the sales ids needs to be different) because the data set is a running file, so once a sales ID is on there it stays on there.
I’m not sure if a column in power query is the best response or if I’m better off using a dax measure?

Thank you in advance!

 

[anvg]

Hello.
A variant

let
    Source = Table.FromColumns({{"name1", "name2", "name3"}}, {"employee"}),
    top = Number.RoundUp(try Number.RandomBetween(2 * Table.RowCount(Source), 3 * Table.RowCount(Source)) otherwise null, 0),
    baseSaleIds = Table.FromColumns({{1..top}}, {"salesId"}),
    addRandom = Table.AddColumn(baseSaleIds, "random", each try Number.Random() otherwise null),
    orderRandom = Table.Sort(addRandom,{{"random", Order.Ascending}}),
    addSaleIdxs = Table.AddIndexColumn(orderRandom, "employeeId", 0),
    toEmployeeId = Table.TransformColumns(addSaleIdxs, {"employeeId", each Number.RoundDown(_ / Table.RowCount(Source), 0)}),
    addEployeeIdToSource = Table.AddIndexColumn(Source, "employeeId"),
    joinSaleIds = Table.Join(addEployeeIdToSource, {"employeeId"}, toEmployeeId, {"employeeId"}),
    removeCalcColumns = Table.RemoveColumns(joinSaleIds,{"employeeId", "random"})

in
    removeCalcColumns

Regards,