Randomize Data in Power Query Based on Certain Criteria

S

seaottr

Board Regular
Joined
Feb 10, 2010
Messages
60
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi PQ Gurus,

I have a tall order, which I know I would NEVER be able to figure out myself, and am hoping one of you experts could assist. I know the requirements are pretty extensive, but would appreciate ANY help in trying to figure this out.

I have a large data set (small sample below), and I want to have Power Query achieve the following:
Randomly select 5 "Chat_ID"s for every "Support Name", but assign it to a "TM_Name" that ISN'T the existing one. The new TM Name should be added to the "New_TM_Name" column. I also want to make sure that every New TM has a (fairly) equal number of Chat_IDs assigned to them.

Note: I realize the sample below won't have enough records to fully complete the above, so if more sample records are required, please let me know.

Support_NameTM_NameChat_IDInteraction_IDHelp_ReasonsSecondary_ReasonsSupport_SummaryIDDateNew_TM_Name
AnikRhonda66297795653865BB-
3​
2023-01-03​
AnikRhonda34387323456068BB-
4​
2023-01-03​
AnikRhonda46733657650617OU-
5​
2023-01-03​
AnikRhonda6945843819664MC-
6​
2023-01-03​
ZaraClayton547107374256OH-
7​
2023-01-03​
JuanLisa80724956662201MO-
8​
2023-01-03​
JuanLisa80724956662201MO-
9​
2023-01-03​
ZaraClayton54687138561283GQ-
10​
2023-01-03​
JuanLisa62048445157343OU-
11​
2023-01-03​
LouiseVivian43744844450331S-
12​
2023-01-03​
LouiseVivian43744844naAH-
13​
2023-01-03​
ZaraClayton92365172564922OU-
14​
2023-01-03​
PatriceJawad70008MC-
15​
2023-01-03​
JuanLisa85794614277427MR-
16​
2023-01-03​
AJawad59250BB-
17​
2023-01-03​
LouiseVivian57322449770251MO-
18​
2023-01-03​
LouiseVivian57322449770251DN-
19​
2023-01-03​
ZaraClayton37592139669594GU-
20​
2023-01-03​
JustinRhonda34172016384709MC-
21​
2023-01-03​
ZaraClayton57389647896765GU-
22​
2023-01-03​
TimRhonda74509688857806GU-
23​
2023-01-03​
AnikRhonda8956385-MR-
24​
2023-01-03​
RichardTyler63754729788071DN-
25​
2023-01-03​
LouiseVivian57322449770251MC-
26​
2023-01-03​
SharonJavier5942842850TT-
27​
2023-01-03​
NancyVivian17249105172221BB-
28​
2023-01-03​
RitaJavier94809509895098MR-
29​
2023-01-03​
MelissaRhonda44719849863288OU-
30​
2023-01-03​
AJawad97576BB-
31​
2023-01-03​
JustinRhonda91329226684OU-
32​
2023-01-03​
JuanLisa1216170221004GC-
33​
2023-01-03​
GinnyJavier8085878382649BB-
34​
2023-01-03​
JulieLisa5737619287943OH-
35​
2023-01-03​
kathleenGregory89336GU-
36​
2023-01-03​
NancyVivian44188206481622BB-
37​
2023-01-03​
TimRhonda22687751463652GU-
38​
2023-01-03​
VanessaLisa51916032685751S-
39​
2023-01-03​
StacyTyler4046019500BB-
40​
2023-01-03​
PatriceJawad722891S-
41​
2023-01-03​
LouiseVivian43744844na1S-
42​
2023-01-03​
DanLisa584538049650VC-
43​
2023-01-03​
RichardTyler8015158920718MC-
44​
2023-01-03​
MelissaRhonda7833434795257OU-
45​
2023-01-03​
ChristineMLisa7666974494401MO-
46​
2023-01-03​
JuanLisa90086500222568BB-
47​
2023-01-03​
ZaraClayton71240875184141MC-
48​
2023-01-03​
JosephJawad16946VO-
49​
2023-01-03​
CherylLisa74426822188OU-
50​
2023-01-03​
NancyVivian98116733520587BB-
51​
2023-01-03​

ANY help would be greatly appreciated!

Thank you in advance!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I am not sure if each Support_Name should be assigned to the same TM_Name or not, but if every new TM should have the equal (fair) number of assignments then TMs should be different for each chat. According to this assumption, here is my way of solving this puzzle. You might need to adjust the ChatCount and Table1 name values in the code.

The main data - Table Name = "Table1"
Support_NameTM_NameChat_IDInteraction_IDHelp_ReasonsSecondary_ReasonsSupport_SummaryIDDate
AnikRhonda66297795653865BB-32023-01-03
AnikRhonda34387323456068BB-42023-01-03
AnikRhonda46733657650617OU-52023-01-03
AnikRhonda6945843819664MC-62023-01-03
ZaraClayton547107374256OH-72023-01-03
JuanLisa80724956662201MO-82023-01-03
JuanLisa80724956662201MO-92023-01-03
ZaraClayton54687138561283GQ-102023-01-03
JuanLisa62048445157343OU-112023-01-03
LouiseVivian43744844450331S-122023-01-03
LouiseVivian43744844naAH-132023-01-03
ZaraClayton92365172564922OU-142023-01-03
PatriceJawad70008MC-152023-01-03
JuanLisa85794614277427MR-162023-01-03
AJawad59250BB-172023-01-03
LouiseVivian57322449770251MO-182023-01-03
LouiseVivian57322449770251DN-192023-01-03
ZaraClayton37592139669594GU-202023-01-03
JustinRhonda34172016384709MC-212023-01-03
ZaraClayton57389647896765GU-222023-01-03
TimRhonda74509688857806GU-232023-01-03
AnikRhonda8956385-MR-242023-01-03
RichardTyler63754729788071DN-252023-01-03
LouiseVivian57322449770251MC-262023-01-03
SharonJavier5942842850TT-272023-01-03
NancyVivian17249105172221BB-282023-01-03
RitaJavier94809509895098MR-292023-01-03
MelissaRhonda44719849863288OU-302023-01-03
AJawad97576BB-312023-01-03
JustinRhonda91329226684OU-322023-01-03
JuanLisa1216170221004GC-332023-01-03
GinnyJavier8085878382649BB-342023-01-03
JulieLisa5737619287943OH-352023-01-03
kathleenGregory89336GU-362023-01-03
NancyVivian44188206481622BB-372023-01-03
TimRhonda22687751463652GU-382023-01-03
VanessaLisa51916032685751S-392023-01-03
StacyTyler4046019500BB-402023-01-03
PatriceJawad722891S-412023-01-03
LouiseVivian43744844na1S-422023-01-03
DanLisa584538049650VC-432023-01-03
RichardTyler8015158920718MC-442023-01-03
MelissaRhonda7833434795257OU-452023-01-03
ChristineMLisa7666974494401MO-462023-01-03
JuanLisa90086500222568BB-472023-01-03
ZaraClayton71240875184141MC-482023-01-03
JosephJawad16946VO-492023-01-03
CherylLisa74426822188OU-502023-01-03
NancyVivian98116733520587BB-512023-01-03

The M Code:
Power Query: 
let
    ChatCount = 5,
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Randomize = Table.AddColumn(Source, "Randomize", each Number.Random()),
    Supports = Table.Group(Randomize, {"Support_Name"}, {{"Data", each Table.Sort(Table.Range(_, 0, ChatCount), "Randomize")}}),
    Data = Table.Buffer(Table.AddColumn(Table.ExpandTableColumn(Supports, "Data", List.RemoveFirstN(Table.ColumnNames(Source), 1)), "New_TM_Name", each null)),
    TMs = Table.AddColumn(Table.FromList(List.Distinct(Source[TM_Name]),Splitter.SplitByNothing(), {"TM_Name"}),"Rate", each 0, Int64.Type), 
    Result = List.Accumulate(
                {0..Table.RowCount(Data) - 1},
                [TMsTable = TMs, DataTable = Data],
                (state, current) =>
                    let
                        Chat = state[DataTable]{current},
                        TM = Table.First(Table.Sort(Table.SelectRows(state[TMsTable], each [TM_Name] <> Chat[TM_Name]), {"Rate"})),
                        TMsUpdate = Table.ReplaceMatchingRows(state[TMsTable], {TM, Record.Combine({TM, [Rate = TM[Rate] + 1]})}),
                        DataUpdate = Table.ReplaceMatchingRows(state[DataTable], {Chat, Record.Combine({Chat, [New_TM_Name = TM[TM_Name]]})}),
                        Result = [TMsTable = TMsUpdate, DataTable = DataUpdate]
                    in
                        Result
            )[DataTable]
in
    Result
And the result:
Support_NameTM_NameChat_IDInteraction_IDHelp_ReasonsSecondary_ReasonsSupport_SummaryIDDateNew_TM_NameTM NameAssignment
AnikRhonda46733657650617OU-52023-01-03GregoryGregory6
AnikRhonda66297795653865BB-32023-01-03JavierJavier6
AnikRhonda6945843819664MC-62023-01-03TylerTyler6
AnikRhonda34387323456068BB-42023-01-03JawadJawad6
AnikRhonda8956385-MR-242023-01-03VivianVivian6
ZaraClayton37592139669594GU-202023-01-03LisaLisa5
ZaraClayton92365172564922OU-142023-01-03RhondaRhonda5
ZaraClayton57389647896765GU-222023-01-03GregoryClayton6
ZaraClayton54687138561283GQ-102023-01-03Javier
ZaraClayton547107374256OH-72023-01-03Tyler
JuanLisa62048445157343OU-112023-01-03Clayton
JuanLisa1216170221004GC-332023-01-03Jawad
JuanLisa85794614277427MR-162023-01-03Vivian
JuanLisa80724956662201MO-92023-01-03Clayton
JuanLisa80724956662201MO-82023-01-03Rhonda
LouiseVivian43744844naAH-132023-01-03Lisa
LouiseVivian57322449770251DN-192023-01-03Gregory
LouiseVivian57322449770251MC-262023-01-03Javier
LouiseVivian43744844450331S-122023-01-03Tyler
LouiseVivian57322449770251MO-182023-01-03Jawad
PatriceJawad722891S-412023-01-03Vivian
PatriceJawad70008MC-152023-01-03Lisa
AJawad97576BB-312023-01-03Clayton
AJawad59250BB-172023-01-03Rhonda
JustinRhonda34172016384709MC-212023-01-03Gregory
JustinRhonda91329226684OU-322023-01-03Javier
TimRhonda74509688857806GU-232023-01-03Tyler
TimRhonda22687751463652GU-382023-01-03Jawad
RichardTyler63754729788071DN-252023-01-03Vivian
RichardTyler8015158920718MC-442023-01-03Lisa
SharonJavier5942842850TT-272023-01-03Clayton
NancyVivian17249105172221BB-282023-01-03Rhonda
NancyVivian98116733520587BB-512023-01-03Gregory
NancyVivian44188206481622BB-372023-01-03Javier
RitaJavier94809509895098MR-292023-01-03Tyler
MelissaRhonda44719849863288OU-302023-01-03Jawad
MelissaRhonda7833434795257OU-452023-01-03Vivian
GinnyJavier8085878382649BB-342023-01-03Lisa
JulieLisa5737619287943OH-352023-01-03Clayton
kathleenGregory89336GU-362023-01-03Rhonda
VanessaLisa51916032685751S-392023-01-03Gregory
StacyTyler4046019500BB-402023-01-03Javier
DanLisa584538049650VC-432023-01-03Tyler
ChristineMLisa7666974494401MO-462023-01-03Jawad
JosephJawad16946VO-492023-01-03Vivian
CherylLisa74426822188OU-502023-01-03Clayton

The code takes care about the "fair" assignment. You can verify that by looking at the table in the result table (scroll right to see that part).
 
Upvote 0
Solution
smozgur said:
I am not sure if each Support_Name should be assigned to the same TM_Name or not, but if every new TM should have the equal (fair) number of assignments then TMs should be different for each chat. According to this assumption, here is my way of solving this puzzle. You might need to adjust the ChatCount and Table1 name values in the code.

The code takes care about the "fair" assignment. You can verify that by looking at the table in the result table (scroll right to see that part).
Click to expand...
THank you SO much smozgur! I've waited so long for a solution that I ended up giving up trying to automate this. Yours is an interesting solution and it might be my only option, so I appreciate the time you spent on this! You're a rock star!
 
Upvote 0
seaottr said:
THank you SO much smozgur! I've waited so long for a solution that I ended up giving up trying to automate this. Yours is an interesting solution and it might be my only option, so I appreciate the time you spent on this! You're a rock star!
Click to expand...
I know it's been a while since you asked the question, but it was an interesting one that I wanted to come up with a solution. Glad to hear it helps :)
Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Randomize PQ Results Based on Certain Criteria
Replies
5
Views
558
JGordon11
JGordon11
muhammad susanto
  • Solved
Power Query : Transpose To Multiple Column Based Criteria
Replies
3
Views
720
lrobbo314
lrobbo314
muhammad susanto
  • Question
Power Query : Replace value with 0 based on criteria name "Mercy"
Replies
6
Views
507
GraH
GraH
muhammad susanto
  • Solved
Power Query : Transpose Column To Rows Then Fill Down Date in Other Column
Replies
5
Views
784
alansidman
A
W
  • Question
Power Query , get record with last data and 1 more criteria
Replies
1
Views
475
lrobbo314
lrobbo314

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top