Forum Discussion
Problem with grouping/counting values
- 1 year ago
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_columnsStarting with this data...
It transforms into...
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?
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?
- SimonSchoutz1 year ago
Helper I
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.