Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DHB
Helper V
Helper V

How 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;

DHB_0-1679289351436.png

Can anyone help me with some code that might achieve this?

 

Thank you,

 

Best,

 

DHB

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1679293406347.png

 

= 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}))

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1679293406347.png

 

= 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}))

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?

Can show more details about error or attach a snapshoot?

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.  

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors