Forum Discussion

Helper I

1 year ago

Solved

Problem with grouping/counting values

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

Need Help

jgeddes

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_columns

Starting with this data...

It transforms into...

9 Replies

PwerQueryKees
Super User
1 year ago
What is your expected result given the sample data?
- SimonSchoutz
  Helper I
  1 year ago
  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

jgeddes

Super User

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_columns

Starting with this data...

It transforms into...

SimonSchoutz
Helper I
1 year ago
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
  Super User
  1 year ago
  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?

dufoq3

Community Champion

1 year ago

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

v-nmadadi-msft
Community Support
1 year ago
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.
v-nmadadi-msft
Community Support
1 year ago
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.

Forum Discussion

Problem with grouping/counting values

9 Replies

Related content

Count of groups of distinct count by group

Count distinct values in one column, grouped by another column

Logic problem?

Group by a count of values

Grouping counts and distinct counts in DAX

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