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
AlienSx's avatar
AlienSx
Icon for Super User rankSuper 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