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
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.
Solved! Go to 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.
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.
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |