cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Georger2323 Regular Visitor
Regular Visitor

Overwrite already existing rows when refreshing

I have a database with dates that is refreshed every X hours. I want once I hit the refresh button to overwrite the rows that have the same Date with the new data.

 

Currently, when I hit the the refresh button I get duplicates of the same date. I don't want that.

How could I fix this?

 

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
vivran22 Member
Member

Re: Overwrite already existing rows when refreshing

@Georger2323 

 

Hi,

 

I feel that in this scenario removing duplicates is a better option to go with as compared to merge table, considering the fact the there is a single souce table.

 

Rgds,

Vivek

 

PS: It is good that you are asking questions. That is how we all learn Smiley Happy

9 REPLIES 9
vivran22 Member
Member

Re: Overwrite already existing rows when refreshing

@Georger2323 

 

Hi,

 

You may use tips from the article having similar issue

 

Rgds,

Vivek

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Georger2323 Regular Visitor
Regular Visitor

Re: Overwrite already existing rows when refreshing

Thank you for the help @vivran22. This is a nice article.

 

However, I want this to be done automatically, once I hit the "refresh" button.

If I have the 2 queries the article says (the original data and the override data), how can I perform the merge and append functions automatically?

 

Thank you.

vivran22 Member
Member

Re: Overwrite already existing rows when refreshing

@Georger2323 

 

Hi,

 

Ususally when you hit refresh, Power Query will perform all those tasks (which includes merge and append) which has been defined in the Edit Query mode. So, your entire process should  be automated will work with the refresh button.

 

Otherwise, you may share the sample files and I would like to recreate it for the demonstration purpose.

 

Rgds,

Vivek

Georger2323 Regular Visitor
Regular Visitor

Re: Overwrite already existing rows when refreshing

@vivran22

 

Sorry for the late reply. I experimented a little and I realised that what I ask for can be done with the "Remove Duplicates" option. Seems more simple solution than creating 2 separate tables.

However, does that option deletes the rows or just hides them? What do you think?

 

Thank you

vivran22 Member
Member

Re: Overwrite already existing rows when refreshing

@Georger2323 

 

If your question is whether Remove Duplicate will make changes in the source data then the answer is it will hide the rows. "Remove Duplicates" will keep the first row of the duplicates row and removes/hides the rest before loading to Power BI desktop.

 

Hope it answers your query.

 

Rgds,

Vivek

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Georger2323 Regular Visitor
Regular Visitor

Re: Overwrite already existing rows when refreshing

@vivran22

My question is if I remove the duplicates, is the size of the data table (or the space it takes) reduced or does it remain the same?

For example if I open a query in power BI with a 10 million row table and then open the same query but now with only 100 rows because the rest are hidden, will they take the same time to load?

Thank you.
vivran22 Member
Member

Re: Overwrite already existing rows when refreshing

@Georger2323 


As per my understanding, Power Query will load the data first and the perform then desired actions. You may improve the efficiency of the entire process by restricting what all tables/queries be refreshed or to be loaded to Power BI data model.


This article may guide you to some extent


Rgds,

Vivek


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Georger2323 Regular Visitor
Regular Visitor

Re: Overwrite already existing rows when refreshing

@vivran22 

 

Because I don't understand how I could apply the advice given on this article I will stick with the 2 solutions we discussed so far. So the 2 solutions for my problem are:

 

1) This article that explains how to override data

2) Hiding duplicate rows

 

The 2nd option is not memory effective.

If I choose to go with the 1st option, how could I create the override table every time?

 

For example, let's say original data table has data for dates 13th-16th of June.

I hit refresh button and new data comes in, for the dates 14th-17th of June. Now I need to replace the already existing 14th-16th of June on the original data table with the new ones, and then append the data for 17th on the same table.

 

To do this, as far as the article says, I will have to create an "override" table, with the new data (14th-17th of June).

But the next time I hit the "refresh" button another bunch of new data will come in, for the dates 15th-18th. What happens now with the override data table? I will need to replace all of the dates on the override table (14th-17th) with the new ones (15th-18th). Am I correct?  If yes, how could I do that?

 

Thank you and sorry for the lot of questions but I am new to power BI

 

vivran22 Member
Member

Re: Overwrite already existing rows when refreshing

@Georger2323 

 

Hi,

 

I feel that in this scenario removing duplicates is a better option to go with as compared to merge table, considering the fact the there is a single souce table.

 

Rgds,

Vivek

 

PS: It is good that you are asking questions. That is how we all learn Smiley Happy

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 897 guests
Please welcome our newest community members: