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
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity 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"})