Forum Discussion

JNelson's avatar
JNelson
Icon for Helper II rankHelper II
2 years ago
Solved

Grouping Function Power Query

Hello all,   I think this is a basic grouping action however I can't get it to work. Or I may need to pivot or have a conditional statement in, but was hoping someone would be able to assist.   I...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hello, JNelson group and expand operations may shuffle data so add index column to sort by in the end if you like.

    let
    Source = your_table,
    fx = (tbl) => 
        if Table.RowCount(tbl) = 1 
        then tbl
        else Table.FromRecords(
            Table.TransformRows(
                tbl, 
                (x) => Record.TransformFields(
                    x, 
                    {"Qty", each if x[A or P] = "A" then List.Sum(tbl[Qty]) else null}
                )
            )
        ),
    group = Table.Group(
        Source, 
        {"Date", "Code"}, 
        {{"x", fx}}),
    xp_tbl = Table.ExpandTableColumn(group, "x", {"Type", "Qty", "Q2", "A or P"})
in
    xp_tbl
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi JNelson, different approach here.

 

Before

 

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzDRNzIwMlHSUTIEYv+izPTMvMQcINPYwABImoMIR6VYHSyKA4ryQSosgIQlSFkApjIjIPZxKlaAKDW0sASSpviVIjnBxBxImEEcEAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, Type = _t, Qty = _t, Q2 = _t, #"A or P" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Qty", Int64.Type}, {"Q2", Int64.Type}, {"Date", type date}}),
    GroupedRows = Table.Group(ChangedType, {"Date", "Code"}, {{"All", each Table.AddColumn(_, "Sum Qty", (x)=> List.Sum([Qty]), Int64.Type), type table [Date=nullable date, Code=nullable text, Type=nullable text, Qty=nullable number, Q2=nullable number, A or P=nullable text]}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    ReplacedQty = Table.ReplaceValue(CombinedAll,
        each [A or P] = "A",
        each [Sum Qty],
        (x,y,z)=> if y then z else null,
        {"Qty"} ),
    RestoreDataTypes = Value.ReplaceType(ReplacedQty, Value.Type(CombinedAll)),
    RemovedColumns = Table.RemoveColumns(RestoreDataTypes,{"Sum Qty"})
in
    RemovedColumns