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

[LuisRossa]

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

[Greg_Deckler]

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

[LuisRossa]

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 ?

[dax]

Hi @LuisRossa , 

You could refer to @Anonymous 's suggestion, if this is not what you want, please correct me and inform more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[LuisRossa]

Hi All,

Thank you for your replies but I'm still stuck.

Let me explain in another way what is I want.

I have a column with an ID and a second column with a value, let's call it Status.

Example : xyz,100

Now I can create a conditional column with the formula if [Status] = 100 then 0 else 1.

So far, so good ...

Now comes what I don´t know how to do it.

I want to create a fourth column that copies the contents of the Status column but in  way that it's contents remain static.

Example:

First run:

xyz,100, 0, 0

Second run:

xyz, 200, 1, 0

The third column, the one with the formula recalculated, but the last column kept the same value of the previous run.

Thanks in advance.

[Anonymous]

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