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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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. 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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]))

14.png

 

15.png

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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]))

14.png

 

15.png

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
Community Champion
Community Champion

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!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors