Copying a calculated column to a "frozen" column

[Bderi]

Hi there,

I am relatively new to Power Pivot/Power Query - I've created a model with a number of calculated columns and (at last) it works fine.

What I'd now like to achieve is to "freeze" some of the calculated columns from being recalculated and store these as fixed values. I've tried ADDCOLUMN, but to no avail.

A simple example: I have as "Budget 2019" as a calculated column dependent on "Forecast Revenue 2018". "Forecast Revenue of 2018" is a also a calculated column which keeps changing, dependent on other columns. At one point in time, I'd like to freeze "Budget 2019" in another column, so it no longer updates (e.g. by setting a "Freeze Budget" variablee" - so even if "Forecast Revenue 2018" is updated, the "Budget 2019" no longer changes. 

Is that possible without manual copying (or manual creation of a linked table) somehow? 

Thanks in advance,

Balazs

[TomMartens · Accepted solution]

Hey,

unfortunately this is not possible, simply because of the following behavior.

Custom Columns (adding columns to a using PowerQuery/M) or

Calculated Columns (adding columns to a table using DAX)

adding structure to a source table. For this reason it will not be possible to cut the ties to a source.

The only way I can think of is to export the data using a table visual and then "reimport" the data and attach the data to your data model. Using export data to csv is limited to 30000 rows, exporting to xlsx is limited to 150k rows and only available from the srvice.

Here you will find more information about export data:

https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-export-data

Regards,

Tom

[Bderi]

Thansk again, Tom, for the quick and clear answer. 

Well, I guess you can't have it all...will go the export/import route.

Balazs