Grouping Rows

[rec161]

I am trying to find a function that will group rows together in the "Incremental" column based on the value of the MFPart column.... following the SequenceNum

Example: IDs 7418314-7418329 would be value "1" in Incremental column, 7418330-7418332 would be "2", 7418334 = "3", 7418333-7418338 = "4"

 

[sandy666]

you want to group MFPart column or ID column?

btw. use shorter column names

 

[sandy666]

use Custom Column, name it Incremental the use this M-code:

if [OrderltemAssemblylD] >= 7413314 and [OrderltemAssemblylD] <= 7413329 then 1 else if [OrderltemAssemblylD] >= 7413330 and [OrderltemAssemblylD] <= 7413332 then 2 else if [OrderltemAssemblylD] >= 7413334 and [OrderltemAssemblylD] <= 7413334 then 3 else if [OrderltemAssemblylD] >= 7413333 and [OrderltemAssemblylD] <= 7413338 then 4 else null

it will give you result below

 

[rec161]

thanks sandy but im trying to make a dynamic column using a formula, there are thousands of rows, not just the sample i showed

 

[sandy666]

your manual criteria has nothing to do with MFPart column,
so maybe be more descriptive with your logic (algorithm) or wait for someone else

 

[rec161]

ill try to simplify the data to clarify what im looking for (sorry for the confusion!)

what im trying to do is increase the value of the IncrementalGrouping column everytime the MFPart number changes (sort sequence based on SeqNum)

does that help?

ID	SeqNum	MFPart	IncrementalGrouping (Calculated Column)
1	10	9044	1
2	11	9044	1
3	12	9044	1
4	13	9035	2
5	14	9035	2
6	15	9044	3
7	16	9044	3
8	17	9044	3

 

[sandy666]

post#6 is completely different from your first post
so maybe this:

 

[sandy666]

correction​

 

[rec161]

yes that is what i am trying to achieve. any suggestions on functions or formula to use?

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"MFPart"}, {{"Count", each _, type table [OrderltemAssemblylD=number, Batch=text, MFPart=text, SequenceNum=number]}, {"Inc", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Group,{{"MFPart", Order.Ascending}}),
    Incremental = Table.AddIndexColumn(Sort, "Incremental", 1, 1),
    Expand = Table.ExpandTableColumn(Incremental, "Count", {"OrderltemAssemblylD", "Batch", "SequenceNum"}, {"OrderltemAssemblylD", "Batch", "SequenceNum"}),
    TSC = Table.SelectColumns(Expand,{"OrderltemAssemblylD", "Batch", "MFPart", "SequenceNum", "Incremental"})
in
    TSC