Forum Discussion

JNelson's avatar
JNelson
Helper 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 have a data set of sales where, if there are multiple rows for a single day and code, I want to SUM the Qty column relating to the Date and the Code and for the summed value to populate in the corresponding row that has A in column A or P. And to leave the corresponding Qty cell (where column [A or P] is P) blank. The order of [A and P] is not always A on top so may need sorting? But don't want to mess up the data. Example of desired outcome below, and data table provided.

 

 

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

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    NewStep=let a=Table.Group(PreivousStepName,{"Date","Code"},{"n",each if Table.RowCount(_)>1 then List.Sum([Qty]) else null}) in Table.ReplaceValue(PreviousStepName,each _,"",(x,y,z)=>let b= a{[Date=y[Date],Code=y[Code]]}[n] in if b=null then x else if y[A or P]="A" then b else null,{"Qty"}) 

  • AlienSx's avatar
    AlienSx
    Super User
    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
    Community 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