How Can I Create Groups of Equal Size Within A Data Set?

[DHB]

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

[wdx223_Daniel · Accepted solution]

= 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?

[DHB]

Hi @wdx223_Daniel I only just saw your message.  In the meantime I found an alternative solution which worked...

• = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)
• = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],5) +1)

Thank you for your help - it certainly got me looking in the right area.