Sum spend of the most recent version in each category

[em_001]

Hi, would someone be able to help me create a custom column in power query editor? 

I have a table with 3 columns:

1: PO (Purchase order #)

2: Revision (each time the dollars spent changes, the PO is revised. The POs are revised 0-3 times)

3: Spend 

Each PO# can have multiple lines to account for:

1: Multiple line items

2: Revisions 

I would like to create a "Current Spend" column in power query that pulls over the spend for the most recent (highest # revision) per PO. This column should have blanks or 0 for any previous revisions of the PO. If 0 revisions were made, it should still pull over the spend for that revision. 

For example: 

Thank you!! 

[AlienSx]

Hello, @em_001 

    f = (tbl as table) as table =>
        [maxr = List.Max(tbl[Revision]),
        cs  = Table.AddColumn(tbl, "Current Spend", each if [Revision] = maxr then [Spend] else null)][cs],
    g = Table.Group(Source, "PO#", {{"rows", each f(_)}}),
    expand = Table.ExpandTableColumn(g, "rows", {"Revision", "Spend", "Current Spend"})

[v-weiyan1-msft · Accepted solution]

Hi @em_001 ,

Please try code as below to create a custom column .

if [Rsvision] = List.Max(
    let
    _PO = [PO]
    in
    Table.SelectRows(#"Changed Type",each _PO = [PO])[Rsvision]
    )
    then [Spend] else null

My Sample and result are as below.

Best Regards,

Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.