Forum Discussion
SimonSchoutz
1 year agoHelper I
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 1...
- 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...
PwerQueryKees
1 year agoSuper User
What is your expected result given the sample data?
- SimonSchoutz1 year agoHelper I
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