Forum Discussion

LuisRossa's avatar
LuisRossa
Regular Visitor
6 years ago

New column: copying existing column with formula and only paste values

Hi,
How can I, using power query, copy a column with a formula and paste the results in another column as values?

Something similar is done in Excel by copy and paste values

Thanks
Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Right click column header, Duplicate Column? Probably not understanding something.

    • LuisRossa's avatar
      LuisRossa
      Regular Visitor
      6 years ago

      Hi Greg,

       

      Thanks for your quick response.

       

      My understanding is if you duplicate a column with a formula both columns will return exectly the same.

       

      In Excel if a have a cell with a formula it's contents are dynamic.

      But if I copy that cell and copy/paste values in another cell, the contents of  the destination cell are static.

       

      Can this be done in Power Query ?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi LuisRossa ,

         

        I think you are after the Expression.Evaluate function. Below is the example code to show how this work in brief.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jDUNtRU0FcwUtJRMgFiEO2bWKERnF9alJwaHZBYlJirYBirqRSrE61krKCrYKKgpWABVWgGxPFwNUBTYByjWKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Formula = _t, #"Param 1" = _t, #"Param 2" = _t, #"Another Formula" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Param 1", type number}, {"Param 2", type number}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Output 1", each Expression.Evaluate([Formula])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Outpu 2", each Expression.Evaluate([Another Formula], [Max = List.Max, Source = #"Changed Type", _=_]))

in
    #"Added Custom1"

         

        The function is quite picky, but there are several very good articles on how to use it on the Internet. It can get as complex as the entire query loaded and executed from a side-load text file :).

         

        Kind regards,

        JB