Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kpatterson2
Regular Visitor

Can we update the dataset without deleting and adding again?

The only way I know how to update my dataset in Power BI is to delete it and add it again, which deletes associated reports and dashboards. If I simply add a new column to my data model, is that what i really have to do to see the changes?  I would think every user would think this is a major problem. 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Typically, I work with Excel or Power BI Desktop programs and build my data model there. You can, under specific circumstances, add a column for instance and the next time you refresh, it will be added to your data model. This does not tend to work if you create calculated columns. You have to remove the calculated columns and then refresh and then add the columns back.

 

If you do it this way, update the data model and then load the PBIX or Excel file and overwrite the old data set, you will not break all of your reports, dashboards, etc.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

13 REPLIES 13
car16
New Member

Does anyone know if it's possible to delete columns from your existing excel source data that you are then using in Power BI?  I have imported an excel file with several columns of information that I do not need in Power BI.  The employee who owns the excel file wants to delete several columns.  When I delete the columns from excel and then refresh in Power BI, I receive an error saying I can't refresh because "the column can't be found".  If I delete it out of Power BI as well, I receive the same error.

 

Any thoughts on this?

Gnoom
Frequent Visitor

I have a similar problem - have to delete and reupload a dataset to make newly added measures work correctly. 😞 Strangely new measures work fine on my other dataset (reuploading works, no need to delete the dataset). Do you know is there any way to avoid this issue? (other than stop adding new measures...)

 

 

Greg_Deckler
Super User
Super User

Typically, I work with Excel or Power BI Desktop programs and build my data model there. You can, under specific circumstances, add a column for instance and the next time you refresh, it will be added to your data model. This does not tend to work if you create calculated columns. You have to remove the calculated columns and then refresh and then add the columns back.

 

If you do it this way, update the data model and then load the PBIX or Excel file and overwrite the old data set, you will not break all of your reports, dashboards, etc.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

Are you saying that if I have a calculated field, there is no way I can update them with new data?

For example:

I have an excel with 4 tabs: 2013 - 2014 - 2015 - 2016

I have uploaded those 4 tabs in BI.  I have append all tables into 1 big table.  So I have all data from year 2013-2016.

I have added some columns with calculations.  For example to calculate total profit, total proc price, etc......

This is working perfectly.

 

I have now next tab in xls: 2017

I have uploaded this in BI, and append in the big table.  This was all working BUT the extra fields I had created does not contain any data for 2017 (total sales, total profit, etc....). 

How can I change this without deleting the whole column and create this again?

There are a lot of charts and tables created with special filters, if I have to create the columns all over again, each time I have new data, it will consume a lot of time, if I take the wrong title, all my charts/tables will be corrupted.

Do you have a  solutions for me?

 

Kind regards

Deborah

Hi,

Are you saying that if I have a calculated field, there is no way I can update them with new data?

For example:

I have an excel with 4 tabs: 2013 - 2014 - 2015 - 2016

I have uploaded those 4 tabs in BI.  I have append all tables into 1 big table.  So I have all data from year 2013-2016.

I have added some columns with calculations.  For example to calculate total profit, total proc price, etc......

This is working perfectly.

 

I have now next tab in xls: 2017

I have uploaded this in BI, and append in the big table.  This was all working BUT the extra fields I had created does not contain any data for 2017 (total sales, total profit, etc....). 

How can I change this without deleting the whole column and create this again?

There are a lot of charts and tables created with special filters, if I have to create the columns all over again, each time I have new data, it will consume a lot of time, if I take the wrong title, all my charts/tables will be corrupted.

Do you have a  solutions for me?

 

Kind regards

Deborah

I am working with my data model in Excel using Power Query and uploading that into PowerBI.com. My data source is an Azure SQL database. I haven't figured out how to refresh it to see the new column, it just keeps refreshing the data but not the schema changes. Are you using Power Query? I've been going to the "Recent Sources" icon and then selecting my Azure db connection and finding the table with the changes and reloading it into the model, in which it adds it with a new name, like "Customers (2)" since I already have "Customers." Then I delete "Customers" and change "Customers (2)" to "Customers" and add back in any calculated columns. Maybe there's an option I'm not seeing?

 

You can overwrite the dataset in PowerBI.com? For me I have only seen it add a new dataset with the same name. And then I have to recreate the dashboard.

If you have custom columns, it will never update the schema, you have to get rid of those first. Get rid of your custom columns first, then just refresh, don't create a new query, should plop your new columns in, add calc columns back.

 

I've never really had success doing what you are doing, something about the rename process just doesn't really work well in my experience.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

So while that sort of solved one problem for me, were you saying earlier that you can overwrite the data set in powerbi.com without deleting any reports or dashboards?

I update my Excel and PBIX data sets and my dashboard stays how it is other than the data being updated. I can't say that I've specifically tested reports, I'll have to test some of that.  When you upload the file, it should prompt you if you want to overwrite the data set.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Ok, I see how you get the message about replacing the file- it appears if you click "Get Data" in the bottom left as opposed to clicking the + sign next to Datasets. However, it did not ask me that question if I picked a file from One Drive, which is where my Excel file is. When I picked "local file" option, it did ask me that question and I've been getting an error though after clicking to replace the file. I tried replacing a different dataset and it worked without an error but not really- I didn't see my new columns. So still a little puzzled

I closed my browser tab and opened a new session and now I see my new columns! 🙂

I found a better work around for anyone that sees this. Instead of removing all calculated columns, add a new "fake" one. This schema is updated and all data shows up after a refresh.

Oh my gosh that worked! Just had to delete the custom columns first, if any exist. I didn't really think I had any because I was thinking in terms of calculated columns, but it had added columns in for the relationships.

 

Other people have told me the same thing about adding a new table and deleting the old one and renaming- that it didn't work for them. But it's not really a better method, still have to add any custom columns in. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors