Add more than one custom column in one step.

[Kthjelltësi]

Hi

How could I add, let's say, 2 custom columns in one step. 
Thank you 😊 

[dufoq3]

Hi @Kthjelltësi, it is possible but why would you need that? It is better to create separate steps especially if you are still learning M code but as you wishL:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsovN1TSUTI0ABMGSrE6YDEjINfIAEzAxYyBXGMDMAEUiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
    AddedColumns = Table.FromRecords(List.Transform(Table.ToRecords(Source), each _ & [ Col2_div = Number.From([Col2]) / 2, Col3_div = Number.From([Col3]) / 2 ]))
in
    AddedColumns

[Kthjelltësi]

Thank you for you reply.

I did not explain very well what I was after - sorry - so I'm going to have another go:

I have this table:

what i am doing is, add a custom column that divides Col2 by 2 then go back and add another custom column that divides Col3 by 2.

what i was wondering is "could this be done in a single step"?

thanks in advance.

[Vijay_A_Verma]

Use this where you will need to replace #"Changed Type" with your last step

= Table.FromRecords(List.Transform(Table.ToRecords(#"Changed Type"), (x)=> Record.Combine({x, [Custom1 = x[col2]/2, Custom2 = x[col3]/3]})))

Complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsovN1TSUTI0ABMGSrE6YDEjINfIAEzAxYyBXGMDMAEUiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", Int64.Type}, {"col3", Int64.Type}}),
    Custom1 = Table.FromRecords(List.Transform(Table.ToRecords(#"Changed Type"), (x)=> Record.Combine({x, [Custom1 = x[col2]/2, Custom2 = x[col3]/3]})))
in
    Custom1

[Vijay_A_Verma]

There are many ways to do it but the best way would depend upon your particular business case. One way is below. We can do through List.Accumulate, Records etc as well. 
In this example, I am extracting First and Last Name

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7NLMlQ8MrPyFPSUTIxVYrViVZyzcksTlRwys8vyQAKmgEFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}}),
    Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Transform(#"Changed Type"[Name], each Text.Split(_," "){0}), List.Transform(#"Changed Type"[Name], each List.Last(Text.Split(_," ")))}, Table.ColumnNames(#"Changed Type") & {"First Name", "Last Name"})
in
    Custom1