Combine archive (historic) table and current year tables (all have same columns) to one

[Anonymous]

Using Power BI I have a large excel worksheet archive going back 10 years. It gets updated once a year with the prior year's data. So it only needs refreshing once a year. I have two other tables with current year data from two different sources. All 3 tables have normalized column names.  I need a way to extract report views that treat all three tables as one (for annual variance reporting and trending).  The PQ append is not the answer. I do not want to add columns. Merge does not seem to be the answer because there is no data in the Archive (dates, transaction num#, etc) that match the current year. Another aspect is that I do not want to refresh the Archive table each time I pull current year data (weekly).  I suspect I need to add a custom column to the Archive by which to join the CY tables. But hope there is an easier way.  Looking forward - the Archive will soon exceed the size limit for an excel worksheet. So it too will need to be separate tables requiring the same join solution... whatever that may be. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

1. the Archive will soon exceed the size limit for an excel worksheet

You could create different excel file to store each year's data, 

Put these excel files in a folder,

Then connect to the folder with Power BI Desktop.

"How to Load Data from a Folder in Power BI"

2.  I have two other tables with current year data from two different sources,

I need a way to extract report views that treat all three tables as one (for annual variance reporting and trending).

If the two tables can be connected to "Archive" table with "date" field,

We could create a calendar table in data model view, then create relationships among them

calendar date = ADDCOLUMNS(CALENDAR(DATE(YEAR(TODAY())-1,1,1),DATE(YEAR(TODAY())-1,12,31)),"year",YEAR([Date]),"month",MONTH([Date]))

Create measures above

current_table1 = CALCULATE(SUM(table1[value]))

current_table2 = SUM(table2[value])

current_10years = IF([current_table1]<>BLANK(),SUM('archive'[value]))

Best Regards
Maggie

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

[Stachu]

without having the sample table I'd still suggest to use append - with unpivoting the columns first/after

so something like this:

customer	product	201601	201602	...
A	X	12	13	0.5

would look like this:

customer	product	Period	Value
A	X	201601	12
A	X	201602	13
A	X	...	0.5

Other than periods in columns (or inconsistent naming, e.g. with spaces/special characters) I cannot think of any other reason for Append adding columns. By default it appends rows for the columns with same names, and only adds the new columns if they're not present in the dataset you append to

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

Proud to be a Datanaut!