append data calculated columns

[AAllmon]

I have tables that contain calculated columns.  I use the calculated columns to create composite keys to create a star schema.  This works great for pulling the data from just one source.  All reports look great.  Now I need to append additional data from another server into the report set for consolidated reporting. 

I have tried duplicating the original tables with new names to populate from my second source.  However, the calculated columns from the original table do not create in the copy.  Is there some hidden secret to make it work correctly?   

[v-micsh-msft · Accepted solution]

Hi AAllmon,

Where are the calculated columns created, under Power BI Desktop, or at the data source side?

If the calculated column is created in Power BI desktop data View, then this column would not be available under Query Editor.

When doing column append, this calculated column will not generate under the newly copied table, it will need to create again in the new table.

A workable way should be create "Custom Column " in Query Editor, this should make the calculated column available when doing the appending.

For custom column, check "Create Custom Columns" part in Common Query tasks in Power BI Desktop.

For calculated column, see: Calculated columns.

If I have any misunderstandings, please feel free to let me know.

Regards

[AAllmon]

I created the calculated columns originally in the Power BI Desktop.  I had not read any recommendations of adding them in the Desktop versus in the data queries.  I saw many warnings that creating columns in the Desktop is memory intensive.  I wish I would have known that it is much more beneficial to add them in the data queries.  I now have to go back and retrofit my project since I have to bring an entirely new location into the reporting from another source.  I am working very hard to keep a star schema structure so that bi-directional links can be maintained and give the greatest amount of flexibility for analysis of the data. 

This is going to be a bit of a problem still for me in that I have to use some of the "Related" table columns in my calculations.  I have not found anything that indicates you can have related calculations in the Query manager. 

I believe that strong consideration should be given to having the ability to Duplicate a table and the calculated columns generate.  Otherwise, aggregating data from different sources is going to be very difficult for us data modeler/report authors and end users. 

Thank you for your review and recommendation.