cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saxenaa Frequent Visitor
Frequent Visitor

Power BI tables loosing calculated columns

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Power BI tables loosing calculated columns

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: Power BI tables loosing calculated columns

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Moderator v-qiuyu-msft
Moderator

Re: Power BI tables loosing calculated columns

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

saxenaa Frequent Visitor
Frequent Visitor

Re: Power BI tables loosing calculated columns

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors