Based on unique value in column A, add new column with values from column B

[faridrzayev]

Hello,

This is my original table:

COLUMN A	COLUMN B
Car	100
Car	100
Bike	80
Bike	80
Bike	80
Skate	45
Laptop	100
Phone	20

The result:

Column A	Column B	Column C
Car	100	100
Car	100	0
Bike	80	80
Bike	80	0
Bike	80	0
Skate	45	45
Laptop	100	100
Phone	20	20

The new Column C will get values from Column B based on distinct values from Column A.

Thank you in advance.

Best Regards,

Farid.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjQwUIrVQec5ZWanArkWhHnB2YklIK6JKZjrk1hQkl+AZFJARn4eSN4IyI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COLUMN A" = _t, #"COLUMN B" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "COLUMN C", each if [Index]>0 and [COLUMN A]=#"Added Index"{[Index]-1}[COLUMN A] then 0 else [COLUMN B]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"COLUMN A", "COLUMN B", "COLUMN C"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"COLUMN C", type number}})
in
    #"Changed Type"