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...
  • jgeddes's avatar
    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...