Every week I download a new data set of our sales for the last two calendar years to-date, in Excel.
We then report the sales by Rep with columns by month over the active period.
Our management team is used to seeing a third, comparison table, that only shows the changes (difference) in totals over the prior week, also by Rep, with columns by month. I am just starting out with the membership training and I see that Sam has recommended having only one Fact table, but as stated here, we are working with two Fact tables: Current and Prior. I think that what we are trying to do here is not that complicated, which is why I selected it as an early-stage project for me, but I am getting stumped with the two-tables issue. Is there a better way?
Do both fact tables share the same column names? If so, we could merge all of the data into one table inside of Power BI. You can then do all of the measures that are needed once the raw data is in Power BI. Also would help if you share a sample PBIX or Excel file.
Thanks
Jarrett
If it is same structure, you can merge the tables. If not, don’t join fact to fact, join fact to dimension tables.
Yes, the column names are the same, but the data overlaps because every week there is more data, so I’m not sure if a merge would work? Sometimes the data on prior weeks/months/years changes, so I do need the whole dataset in it’s current ‘state’ each week, and then to compare it to last week’s ‘state.’
Post a sample PBIX file with both tables separate, and I will try and merge.
Thanks
Jarrett
Very easy to do. All I did was in the Query Editor I selected both tables, then clicked on Append Queries dropdown and select Append Queries As New. I kept the original tables in here just to show that it did append the tables correctly, but you can hide them if you wish in Report View. I have also attached an article & video from Reza Rad regarding the subject matter.
EDNA- Append Tables Solution.pbix
(176.2 KB)
Thanks
Jarrett
1 Like
Ah that totally worked, thank you! On to the next step. . .
1 Like