Forum Discussion
Helper VHow Can I Create Groups of Equal Size Within A Data Set?
Hi,
This is an example of what I'm trying to do in Power Query. I have a list of records (20 IDs A-T) and want to assign them to four groups of 5 (GROUP) with a number for each member within the group (GROUP_REF). So I'm trying to create the middle and right hand columns in this example;
Can anyone help me with some code that might achieve this?
Thank you,
Best,
DHB
= let IDs={"A".."T"} in #table({"ID","Group","Group_Ref"},List.Transform(List.Positions(IDs),each {IDs{_},Number.IntegerDivide(_,5)+1,Number.Mod(_,5)+1}))
4 Replies
- wdx223_Daniel
Community Champion
= let IDs={"A".."T"} in #table({"ID","Group","Group_Ref"},List.Transform(List.Positions(IDs),each {IDs{_},Number.IntegerDivide(_,5)+1,Number.Mod(_,5)+1}))
- DHB
Thank you wdx223_Daniel that works perfectly. When I try to apply it to a data set where the "ID" field is called "PERSON_CODE" and the values are random strings of 7 numerical characters (e.g. 1234567, 7654321...) it errors. Do you know of a tweak I could apply to get it working again?
- wdx223_Daniel
Can show more details about error or attach a snapshoot?
