How to append data from another table upon refresh?

[Anonymous]

Hi

I want to append a table data with other table, where table one is refreshed with new data. So how can i append the final table with a refresh.

E.g: Table1: 

Table 2: 

Once the table 2 is updated upon refresh, Table 1 should be appended with table 2 data as below:

I tried with Append Queries and try to remove 'Inclue in report refresh' option in query editor on the table1, but it didn't help.

Can you suggest what is the best way to append these two tables upon refresh?

Thanks

[venal]

1. Table1

2. Table2

3. Home >> Edit Queries >> Edit Queries >> Append Queries

4. Append Queries Screen >> Two Tables (Enable the radio button) >> Table to append >> select "Table2" >> Click on "Ok".

5. Once it's done, right click on the table2 and disable the "Enable Load" and enable the "Include in report refresh".

6. So that you will get the latest data in Power Bi.

7. If you enable the "Enable Load" for table2, it will load into the data model and it will display in the "Data View and Report View".

8. As both tables data are appended into Table1, better to disable the "Enable load" to avoid the performance issues.

Hope it will help you to resolve your query.

[Anonymous]

Hi @venal ,

Sorry for coming back late. But I think this doesn't seem to update the data from previous and append it. I could still see that table is just refreshed with new data (new date). 

How can I make this workable?

This is my changes done as you suggested:

Table 2: with latest data as per date.

Table 1: Table which needs to be appended with Table 2. This has data for previous dates. 

Let me know if I missed anything?

The table 1 has to be appended, but it seems to be replacing the data from table 2.

Regards

[venal]

Please refer the below link to download the PBIX file.

http://www.mediafire.com/file/qit6pvhvitpja3x/nchawda-append.pbix/file

If this post was helpful may I ask you to mark it as solution and give it some kudos?

[Anonymous]

Hi @venal 

It doesn't work. Every time the table 2 is replaced with new data and that data has to be appended to table 1. But here it happens that everytime the table 2 is replaced with new data for each day, the same data is being replaced in the table 1.

How can I make sure that data from table 1 is retained for previous dates? 

Thanks

[Anonymous]

Can anyone help me on this please? I'm struck over this for couple of days.

@Greg_Deckler @venal @AnkitBI

[venal]

@Anonymous 

https://www.mediafire.com/file/pio5449hqd3aqbv/nchawda-append.pbix/file

Please check with the above link to get the data into Table2.

[Anonymous]

hi @venal 

I think you are not getting what I want, Let me explain the situation again:

Table 2 runs a API call and fetches data into it for every day. When I say it fetches the data, the old data for previous days is replaced completely with new data for current date

So Table 1 should append the data from Table 2, it should have data from previous date(coming from table 2 for previous date) and also data from current date coming from table 2. 

As per your suggestion, it works only if both the tables have same data otherwise it doesn;t. The table 2 is replacing old data with new one , so while appending to table1 it replaces old data as well.

What I wanted:

Table 2 : -> Run a API call, fetch the data and format into the table for current date( which already done & works fine). 

Table 1: -> Append data from Table 2 for each date and keep data for all dates. ( which I wanted to achieve - Blocked)

I hope I'm clear this time. 

[AlexaderMilland]

This should not be done in power bi. Power BI is not ment to archive/store/persist adta like this. 

[ImkeF]

Without a Premium capacity where you could use the incremental load feature, there is no native way in Power BI to achieve this.

Best workaround is probably to use an R- or Python-script to export the imported data to a csv-file like described here: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or... 

[venal]

For table2 - disable the "Enable Load" in Edit Queries and enable the "Include in report refresh".