Forum Discussion

Kthjelltësi's avatar
Kthjelltësi
Frequent Visitor
2 years ago

Add more than one custom column in one step.

Hi

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

Tips and Tricks

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    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

     

     

  • Kthjelltësi's avatar
    Kthjelltësi
    Frequent Visitor
    2 years ago

    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's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      2 years ago

      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

       

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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