Forum Discussion

jiexika24's avatar
jiexika24
Frequent Visitor
2 years ago
Solved

Create a Custom Column based on multiple columns

Hello, I'm trying to create a custom column (column E) that evaluates columns A through D to find cells with a whole number that is greater than or equal to 0 and puts that number into column E. If t...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jiexika24  

    Thanks fot your quick reply, you can refer to the following steps.

    In power query, select all columns, then select 'Merge column' 

    Then you can input the new column name

    After merging, change the column type to whole number.

     

    Output

    Best Regards!

    Yolo Zhu

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

Thanks for the solutions wdx223_Daniel  and Anonymous  provided and i want to offer some more information for user to refer to.

hello jiexika24 , based on your description, you can merge the columns directly, you can refer to the following code to advanced editor in power query.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRsgRiEIrVAXMhyNAAlY/EM4JxTbHJEsMzxCFpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([A], "en-US"), Text.From([B], "en-US"), Text.From([C], "en-US"), Text.From([D], "en-US")}, ""), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged", Int64.Type}})
in
    #"Changed Type1"

 

Output

 

 

Best Regards!

Yolo Zhu

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

  • jiexika24's avatar
    jiexika24
    Frequent Visitor
    2 years ago

    Thank you Anonymous ! I really appreciate your help and see how this would work, but I should have mentioned that the example table I provided above isn't my actual data (since mine has PII).

     

    Would it be possible to rework the expression so that it's not linked to a Json file? I can plug in my variable names, since they are lengthy (e.g., [#"Trainer: First Last Name - Techniques"]).

     

    Thanks for any additional support you can provide!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi jiexika24  

      Thanks fot your quick reply, you can refer to the following steps.

      In power query, select all columns, then select 'Merge column' 

      Then you can input the new column name

      After merging, change the column type to whole number.

       

      Output

      Best Regards!

      Yolo Zhu

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