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

[v-nmadadi-msft]

Hi @SimonSchoutz,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @SimonSchoutz ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[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 · Accepted solution]

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