grouped rows in power query to show the amount in one column once, not a sumation

[Anonymous]

I have a huge table with multiple PO's, in my case the PO amount is a gross sum, and then there are different lines that are there to show how much of that gross amonut was spent.  I need to see all the lines that show the amount spent, but not have the gross amount be multiolied by how many rows there are. I cant find a way to only show the un multiplied gross amount when I am grouping the rows.

Below is an example of what I am trying to see as a result of this.  The PO Amount is the total gross amount allocated to the PO, and the amount spent shows the individual purchases that have used some of the gross amount and is shown in the total line.

                        PO #      Gross amount    spend

                     123456        $4500               $1300

                     123456        $4500               $1200

totals            123456        $4500               $2500

[AlB · Accepted solution]

Hi @Anonymous 

Just group on the first two columns.

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRUjExNTAA0YbGQDpWB6uUEUgqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t, #"Gross amount" = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}, {"Gross amount", Currency.Type}, {"Spend", Currency.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #", "Gross amount"}, {{"Total spend", each List.Sum([Spend]), type nullable number}})
in
    #"Grouped Rows"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.