Create Column

[shri0025]

Hi  Team 

Please help me    i need  new Column     Gate entery Basis  if  gatey entery multipule  i need single amount  Freight amount   Like my output.

Gate entry	Freight Amount
CDA1031293	12850
CDA1031293	12850
CDA1031298	12000
CDA1031298	12000
CDA1031298	12000
CDA1031298	12000
CDA1031298	12000
CDA1031331	12000
CDA1031331	12000
CDA1031411	12100
CDA1031411	12100
CDA1031424	12100
CDA1031424	12100
CDA1031424	12100
CDA1031424	12100
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031493	12200

Output

Gate entry	Freight Amount	Create Column
CDA1031293	12850	12850
CDA1031293	12850
CDA1031298	12000	1200
CDA1031298	12000
CDA1031298	12000
CDA1031298	12000
CDA1031298	12000
CDA1031331	12000	12000
CDA1031331	12000
CDA1031411	12100	12100
CDA1031411	12100
CDA1031424	12100	12100
CDA1031424	12100
CDA1031424	12100
CDA1031424	12100
CDA1031482	12800	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031482	12800
CDA1031493	12200	12200

[v-zhangti · Accepted solution]

Hi, @shri0025 

You can try the following methods. Adding an index column in the Power Query.

Dax column:

Column = Var _minindex=CALCULATE(MIN('Table'[Index]),FILTER('Table',[Gate entry]=EARLIER('Table'[Gate entry])))
Return
IF([Index]=_minindex,[Freight Amount])

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnZxNDQwNjSyNFbSUTI0sjA1UIrVISxsARY2MKCbsLGxIQnCJoYQYUPihI1MaCdsYQQJweEtDE0nRiDhWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Gate entry" = _t, #"Freight Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Gate entry", type text}, {"Freight Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Gate entry"}, {{"Count",
 each  Table.AddIndexColumn(_ , "indx",1,1)}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Gate entry", "Freight Amount", "indx"}, {"Gate entry", "Freight Amount", "indx"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Result", each if[indx] =1 then [Freight Amount] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"indx"})
in
    #"Removed Columns"