Problem with grouping/counting values

[SimonSchoutz]

Hello everyone,

i receive data of weighting process, the data i receive are as following:

Component	Batch	Weight
A	12	0,2
B	13	1
C	14	3
D	15	2,1
E	16	0,9
E	16	1
D	15	2,2
C	14	3,1
B	13	1
A	12	0,2

I want to add a column to count "weight procedure", like this, so when all components of the mixture added to the weighting process, it should increase the value:

Component	Batch	Weight	Weight procedure
A	12	0,2	1
B	13	1	1
C	14	3	1
D	15	2,1	1
E	16	0,9	1
E	16	1	2
D	15	2,2	2
C	14	3,1	2
B	13	1	2
A	12	0,2	2

Now there is a problem. Sometimes, it could happen, when one batch of a component is not sufficient for required amount, there are used 2 batches for example like this:

Component	Batch	Weight
A	12	0,2
B	13	1
C	14	3
D	15	1
D	15-2	1,1
E	16	0,9
E	16	1
D	15	2,2
C	14	3,1
B	13	1
A	12	0,2

As you can see, there are in total 2 issues:

1) My data are alternating beginning to count from component A -> component E and then from component E -> component A before it repeats.

2) Sometimes there are not only 5 batches in one mix but 6 or 7, so i can´t simply count to 5 until i add +1 to "Weight procedure" number.

Is there a clever way to add a column to count the number of weight procedure, when all components are added?

Looking forward to your ideas.

Kind regards,

Simon

[dufoq3]

Hi @SimonSchoutz, what about this?

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY87EsMwCETvQk3BT8Iq7cgjV7mAxve/RoScJplRB/uWBXqHHRCqKBVNPsoEN3Y4Qjy9EZWsZTSCOsErQBM7nLOEi5EnqF/QnNgj06Z8jmqvbxI2Jgp/XujpJ+Zysi1iFNNqMa1OfSb+HrOQ7w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component = _t, Batch = _t, Weight = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Weight", type number}}, "en-US"),
    CompDistinct = List.Buffer(List.Distinct(ChangedType[Component])),
    Comp = List.Buffer(ChangedType[Component]),
    GenWeightProcedure = List.Generate(
        ()=> [ x = 0, y = {Comp{x}}, z = false, w = false, q = 1 ],
        each [x] < List.Count(Comp),
        each [ x = [x]+1,
               y = if z then {Comp{x}} else [y] & {Comp{x}},
               z = List.ContainsAll([y] & {Comp{x}} , CompDistinct),
               w = if z then true else false,
               q = if [w] then [q]+1 else [q] ],
        each [q] ),
    Merged = Table.FromColumns(Table.ToColumns(ChangedType) & {GenWeightProcedure}, Value.Type(Table.FirstN(ChangedType, 0) & #table(type table[Weight Procedure=Int64.Type], {})))
in
    Merged

[jgeddes]

Here is a sample code you can start from...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVBJEsAgCPsLZ2UqLu212ysc//+NijpW8cKQIAkxRjhBgaFcNiRIKsLFhOVS4M2dy8UW+DD0fVqh5n2DlXq5D0XQD4SuHm6k7GDb18zsQ23+39F8xJ1Ljko02+WNFxoo0hLuIu+UpmgESYgVPGZNI3+Y2nfMd6UP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component = _t, Batch = _t, Weight = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Batch", type text}, {"Weight", type number}}),
    add_zero_index = Table.AddIndexColumn(set_types, "Index", 0, 1, Int64.Type),
    add_isSequenced = Table.AddColumn(add_zero_index, "isSequenced", each try if Number.Abs(Number.From(add_zero_index[Batch]{[Index]+1}) - Number.From([Batch])) = 1 then "yes" else "no" otherwise null, type text),
    add_local_group_values = Table.AddColumn(add_isSequenced, "Local Group Values", each if ([Component] = "A" or [Component] = "E") and [isSequenced] = "yes" then [Component] else null, type text),
    fill_down = Table.FillDown(add_local_group_values,{"Local Group Values"}),
    remove_columns = Table.RemoveColumns(fill_down,{"Index", "isSequenced"}),
    local_grouping = Table.Group(remove_columns, {"Local Group Values"}, {{"All Rows", each _, type table [Component=nullable text, Batch=nullable text, Weight=nullable number, #"Local Group Values"=text]}}, GroupKind.Local),
    add_weight_procedure = Table.AddIndexColumn(local_grouping, "Weight Procedure", 1, 1, Int64.Type),
    expand_columns = Table.ExpandTableColumn(add_weight_procedure, "All Rows", {"Batch", "Component", "Weight"}, {"Batch", "Component", "Weight"}),
    remove_local_group = Table.RemoveColumns(expand_columns,{"Local Group Values"}),
    reorder_columns = Table.ReorderColumns(remove_local_group,{"Component", "Batch", "Weight", "Weight Procedure"})
in
    reorder_columns

Starting with this data...

It transforms into...

[SimonSchoutz]

Wow, thank you for your quick support. I think i got an issue with the sequence section.

Batch could also contain letters and not only numbers for example:

Component	Batch	Weight
A	D2309357	5
B	DE7G009639	2,3
C	DG24B7162B	1,1
D	DG24G7017A	4
E	ADN0214100	16
E	ADN0214100	15
D	DG24H7048A	3,5
C	DG24B7162B	1,0
B	DE7G009639	2,5
A	D2309357	4,5

I think, this breaks the seqeuence method listed above, right?

One thing, which might help in building a sequence, component E has always the biggest weight number. So maybe it makes sense to add up the weight numbers until i reach a certain threshhold, in this case (5+2,3+1,1+4+16 = ) 28,4, so if value is e.g. >25, next measurement should be next weight procedure, right? 

[jgeddes]

It does break the sequence. 
What would the Batch number look like there were two or more batches in a single sequence? In your first example they were differentiated with '-2' etc. Would that hold true with your new example data?

[SimonSchoutz]

Sorry for my late reply. You can see in the post above your comment an example for real batch numbers. For example component D uses batch number DG24G7017 and DG24H7048A. There is not only a "-2" added to the batch number. Sorry for confusion.

[PwerQueryKees]

What is your expected result given the sample data?

[SimonSchoutz]

Result should look like this:

Component	Batch	Weight	Weight procedure
A	12	0,2	1
B	13	1	1
C	14	3	1
D	15	1	1
D	15-2	1,1	1
E	16	0,9	1
E	16	1	2
D	15	2,2	2
C	14	3,1	2
B	13	1	2
A	12	0,2	2