locked
Turn Calculated Field into a permanent field RRS feed

  • Question

  • Hi all,

    I am working with a significant amount of data.  I have created several calculated columns with if statements leading me to a final calculated column with the final answers for each row.   All of the calculated columns to get to this final calculated column are taking up lots of space/data.  I was wondering if there is a way to convert my "final" calculated column into a permanant column that is no longer calculated, but are just values with the final answer, so I can get rid of the other columns that are leading to the "final column"

    In simple words, I am trying to change a calculated column, into a text column that is permanent and isn't driven off of anything.

    Is there anyway to do this in powerpivot?  

    Friday, November 21, 2014 4:05 PM

Answers

  • There is no way to convert a calculated column into a "normal" column within Power Pivot alone.

    The best case would be to get DBA support (if you're getting data from a database system) to create a view that calculates your necessary column so it will be there when you process the model.

    The next best option would be to build your own ETL, using a tool like Power Query, for example, to perform the calculation before the data is loaded.

    The next option would be to combine the intermediate columns into a single monolithic calculation in the final column, utilizing SWITCH() and IF() functions nested into a single calculation.

    You could also write a DAX query to load into an Excel table and then load that table back into the model. This works only if your data is <1M rows, and is the most work to perform a refresh on. Some details on writing such a query.

    • Proposed as answer by Michael Amadi Friday, November 21, 2014 11:18 PM
    • Marked as answer by Michael Amadi Sunday, November 30, 2014 12:57 AM
    Friday, November 21, 2014 4:22 PM