Forum Discussion

SimonSchoutz's avatar
SimonSchoutz
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 1...
Need Help
  • jgeddes's avatar
    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...

     

dufoq3's avatar
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