create new 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

My Out put Like This 

Gate entry	Freight Amount	Create Column (Need this Result)
CDA1031293	12850	12850
CDA1031293	12850	0
CDA1031298	12000	1200
CDA1031298	12000	0
CDA1031298	12000	0
CDA1031298	12000	0
CDA1031298	12000	0
CDA1031331	12000	12000
CDA1031331	12000	0
CDA1031411	12100	12100
CDA1031411	12100	0
CDA1031424	12100	12100
CDA1031424	12100	0
CDA1031424	12100	0
CDA1031424	12100	0
CDA1031482	12800	12800
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031493	12200	12200

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

IN DAX
https://1drv.ms/u/s!AiUZ0Ws7G26Rixu4oV5LW2VcErUY?e=4oFKH2

[Anonymous · Accepted solution]

Hi @shri0025 ,

Please try below steps:

1. add an index column in Power Query pane

2. create a new column with below dax formula

Column =
VAR cur_entry = [Gate entry]
VAR cur_index = [Index]
VAR tmp =
    FILTER ( 'Table', [Gate entry] = cur_entry )
VAR _a =
    MINX ( tmp, [Index] )
RETURN
    IF ( cur_index = _a, [Freight Amount], 0 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

IN DAX
https://1drv.ms/u/s!AiUZ0Ws7G26Rixu4oV5LW2VcErUY?e=4oFKH2

[shri0025]

Hi 

Thanks  Ahmedx  Your  Solution is Perfect  but  i need in column Dax not in Query Because My data set is  Many Column   Column 1   to Column 45   so  I dont use  Query only used   Column in Dax . 

[Ahmedx]

to solve a problem in DAX you need an index or id column

[shri0025]

Hi 

Can you see me My out put column If Multipule Gate entery  then Freight AMount single and 0 

Gate entry	Freight Amount	Create Column (Need this Result)
CDA1031293	12850	12850
CDA1031293	12850	0
CDA1031298	12000	1200
CDA1031298	12000	0
CDA1031298	12000	0
CDA1031298	12000	0
CDA1031298	12000	0
CDA1031331	12000	12000
CDA1031331	12000	0
CDA1031411	12100	12100
CDA1031411	12100	0
CDA1031424	12100	12100
CDA1031424	12100	0
CDA1031424	12100	0
CDA1031424	12100	0
CDA1031482	12800	12800
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031482	12800	0
CDA1031493	12200	12200

[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"

[devesh_gupta]

@shri0025 Try to create a calculated column using this DAX:

Create Column = 
VAR GateEntry = YourTableName[Gate Entry]
VAR FreightAmount = YourTableName[Freight Amount]
VAR GateEntryCount = CALCULATE(COUNTROWS(YourTableName), ALLEXCEPT(YourTableName, YourTableName[Gate Entry]))
RETURN
IF(GateEntryCount = 1, FreightAmount, 0)

If you find this insightful, please provide a Kudo and accept this as a solution.

[shri0025]

 Hi Devesh 

Thanks for reply    it is not  working 

[devesh_gupta]

@shri0025 Can you specify any error that you're getting?