Lost Relationships after Append

[WillyF]

Hi Guys,

I created a model where I have around 5x tables and relationships between them. All going well.

It is for financial reporting for the Month of July. 

Now I got the financial results for the month of August, I loaded the August data into the model (same columns and structure of July data). I now have 6x tables (1x July Data, 1x August Data and 4x other small data for calculation purposes).

I changed July Data table name to CombinedData table. All model visuals working well and referring to this CombinedData table (which had July Figures). 

Now I clicked on Append as new, I chose both tables CombinedData (that has July figures) and August Data table into a new table automatically called Append 1. 

I deleted CombinedData table (that had July Figures) and renamed Append 1 as CombinedData (now having July and August Figures).

The model almost worked well. However, the relationships for the initial CombinedData table with other tables have been lost when I deleted it. There were no relationships created automatically by Power BI for the newly created table Append 1 which I renamed to CombinedData. I had to manually recreate all relationships. Now the model is working fine and I can switch between July and August numbers.

1. Is my approach correct to load the new month data into the model? I will be doing the same for the upcoming months as well.

2. Is there a better, most preferrably automatic way to restore same relatioships of the old CombinedData table to the new one?

3. In one of the explanation videos I have seen, I saw someone righ clicking on one of the tables and chosing Reference. I do not know why is that and is it required? if yes for which table and what is the purpose of it!?

Thanks.

[Thejeswar · Accepted solution]

Hi @WillyF ,

What you have done is OKish solution. Nothing majorly wrong with it. Just reordering few steps would do the necessary.

A Better approach would be to not "Append as New". Instead Appending it to the same July table would have preserved the relationships at the same time you would have a single table with both July and August data. Renaming won't affect the relationships.

Benefits:

1. You don't have to delete the old table which will remove all the relationships.

2. Since you are not deleting, you don't have to recreate as well

3. This will satisfy your requirement of automatically setting the relationships too

On your 3rd question for creating references, it is just that it will create a new table that will be same as the source table. For ex: after importing a table (say t1), you are making few transformations to it in Power query. Now if you create a reference of t1 (say t2), this t2 will be identical to t1 at all times. Any future changes you do to t1, will auto reflect in t2. But this has nothing to do with your requirement.

Regards,

[WillyF]

Awesome so next month when I get September data, i click on CombinedData table (that already now have July and August) press Append Queries, choose the second table as September and that's basically it? No new table will be created and no relationships will be lost and will preserve CombinedData table structure and connections to its visuals?

[Thejeswar]

@WillyF ,

Yes that is right. 

One thing to keep in mind is, doing each of these appends will involve additional processing and in the long run may slow down your report.

So although this works without hazzle, I would suggest that you find ways to append the data even before it comes to power bi if the no of months you want to have the data for will continuouslsy increase.

Regards,