Power BI tables loosing calculated columns

[saxenaa]

I have number of calculated columns in a table which is linked to an excel sheet, every now and then I need to update the excel sheet with new data, 

So I copy the new file to the one linked to BI, but doing so when I refresh the data in BI it looses all calculated columns and resets to what are in excel,

this is quite irritating and time consuming to add back the calculated columns, is there a way to handle this?

[v-qiuyu-msft · Accepted solution]

Hi @saxenaa,

Based on my understanding, you get data from a Excel file name in Power BI desktop, and create some calculated columns. There is a new Excel file which has the same name, you replace the old Excel with this new one, then refresh the data in Power BI desktop, calculated columns disappear, right? 

If the new Excel file doesn't have the columns which used in calculated columns, when you refresh the data the error will throw out and the calculated column still exists. If all used columns by calculated columns exist, refresh will get updated data without any error. 

In your scenario, when you replace the Excel file, make sure the new Excel file has the same structure as the original one, eg: Sheet name, column names. 

Best Regards,
Qiuyun Yu 

[saxenaa]

Yes that has been the scenario, and I am expecting that excel data can be little different , so is there a way to handle this at powerquery level so that I don't loose the calculated columns in BI

[Anonymous]

When you create calculated columns in Power BI, those columns are saved into your PBIX file.  These columns are not saved into the excel file.  Power BI does not write back to data sources, it only reads from them.

Any calculated column you create is linked to the Query you created, rather than the data source itself.  So if you reconnect the same file, even inside the same project, but it is in a different query then you won't get your calculated columns.