Power Query Split by Table Group by Rows into a new set of group by tables

[remeng]

Hi,

I am new to power query and was looking for some help with splitting a group by table into two different group by tables if there are more than 8 records in the first resulting table.

Current order of operations:

1) Take the raw data and group it
2) Any returned record that's table has more than 8 rows (records) needs to be split into 2 groups or more that only can contain 8 records each

For instance, if you take a look at row 9, Column "Total Record Count" you will see that there are 15 records to that "Face Pick List Table" columns. Instead of there being 15 to that record, I need it to be split into two groups. Group 1 = 8 records and Group 2 = 7 records.

For example:
1 to 8 records = 1 group
9 to 16 records = 2 groups
16 - 24 = 3 groups
etc.

I tried using ChatGPT to help out but it can "split it" but doesn't carry the rows into the new split lists. It sort of is useless.

How can I actually do this?

Thanks,

Mike

 

[combo]

You can add subindex to each 'Face List Table' tables, expand, split subindex to GroupID, group again using GroupID (if grouping is necessary.
An example with two tables:
1. 'query1':

x1

100 (unique values)

20 ...

2. 'query2' (related table):

x2 (related to 'x1'0	y
100	val1
100	val2

M for merging tables, adding index and subindex, calculating GroupID from subindex, merging subgroups:

let
    Source = query1,
    #"Merged Queries" = Table.NestedJoin(Source, {"x1"}, query2, {"x2"}, "query2", JoinKind.LeftOuter),
    #"Added Index 1" = Table.AddIndexColumn(#"Merged Queries", "Index1", 1, 1, Int64.Type),
    // adds subindex to tables in 'query2'
    #"Added Index 2" = Table.AddColumn(#"Added Index 1", "Index2", each Table.AddIndexColumn([query2],"Index2",1)),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Index 2", "Index2", {"y", "Index2"}, {"y", "Index2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"query2"}),
    #"Add GroupID" = Table.AddColumn(#"Removed Columns", "GroupID", each Number.IntegerDivide([Index2]+7,8)),
    // note the 'all rows' grouping - just to have tables in cells
    #"Grouped Rows" = Table.Group(#"Add GroupID", {"x1", "Index1", "GroupID"}, {{"y", each _, type table [x1=nullable number, Index1=number, y=number, Index2=number, GroupID=number]}})
in
    #"Grouped Rows"

 

[remeng]

Thanks Combo. It didn't work the way I expected, but it got me on the correct path.