Add column with a total based on 2 other columns

[Moniek]

Hi,

I have a data table, I want to add a new column the "wish" column. In excel I know how to do, but how can I do it in Power Query to add the wish column? Additional info is in the image.

Thanks

[m_dekorte · Accepted solution]

Hi @Moniek ,

You could use Group By on the key columns you identified, that will return unique combinations. With some additional M code to extract the first value and repeat 0 for additional rows.

Copy this script into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSkktUgCxHIHYF4gNDYBAKVYnWskIQ94PRd4YLg9S6QTEAUBsZAqVNoFLgxQ6A3EgEBuDdccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Order = _t, Type = _t, Label = _t, Amount = _t]),
    AddColumn = Table.Group(Source, {"Order", "Type"}, 
        {
            {"t", each Table.FromColumns( Table.ToColumns(_) & { {[Amount]{0}} & List.Repeat({0}, List.Count([Amount])-1)}, Table.ColumnNames(_) & {"Wish"}  ) }
        }
    )[[t]],
    ExpandTable = Table.ExpandTableColumn(AddColumn, "t", {"Index", "Order", "Type", "Label", "Amount", "Wish"})
in
    ExpandTable

 If you want to transfer this into your own code replace "Source" here

AddColumn = Table.Group(Source

With the previous step name in your query and you should be good to go.

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!