Forum Discussion

shri0025's avatar
shri0025
Helper II
2 years ago
Solved

create new Column

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 ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Ahmedx
Super User
2 years ago

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"