Forum Discussion
Help comparing two excel worksheets in Power BI
Hi everyone!
So I've got an issue with a project I'm doing. I created a dashboard with some info that I got from an excel workbook containing diferent processes and number of files from all 3 quarters of 2016. Now that the year has finished, I recieved another file of excel with the info updated, including all 4 quarters of 2016. So now I would like to subtract the total number of files I got from this new workbook from the old one to see how many new files were generated this past 4th quarter of 2016.
How can I update my power bi report (with the info of the first 3 quarters of 2016) with the new data that I got? I need to highlight and specially know the processes and files generated this last 4th quarter of 2016. If all this info was in one file, then I would have no problems, so I emphasize the fact that I got two diferent Excel worksheets.
I remain at your disposal to clarify any doubts if you don't understand my issue.
Thanks a lot in advance!
Enzo
Hi enzo2011
Did you find a solution for your issue?
I managed to reproduce your example and came up with this.When importing both of your worksheets, add a custom column to each of them, let's call it "IndexCol" having the values "A" and "B" for each table respectively.
Now that both tables are there with a "flag" for each one, go to edit queries and choose to append queries as new
Choose both tables, one as primary and one as secondary, apply and close.
In your report view, you will now see a new table having all of table A and table B elements.
Create a new calculated column as follows:Q4Files = CALCULATE(SUM(Append1[Files]), Append1[IndexCol]="B") - CALCULATE(SUM(Append1[Files]), Append1[IndexCol]="A")
Choose the table visual and add to it Process and Q4Files, make sure you are making your selection from the newly created table Append1 and that Q4Files is groupes as sum.You should get the following
Tell me how it works!
EDIT: if you have unique process values in both tables, why don't you create a 1:1 relationship between both worksheets on the process. Then create a calculated column on Table2 as follows: Q4Files = Table2[Files]-RELATED(Table1[Files])
7 Replies
- Greg_Deckler
Community Champion
Not exactly sure of your issue, but it sounds like an Append query would be what you want to do in order to take your original query and append all of the rows of your new workbook to it.
First of all thanks for the quick response.
The problem is that I cannot append the two workbooks, because then I'd be double counting files twice. Let me clarify it. In workbook A I've got data of the 1st/2nd/3rd quarter of 2016. And in workbook B I've got data of the 1st/2nd/3rd/4th quarter of 2016. Therefore if I wanna know what's the new data added in workbook B, I should do B-A.
v-ljerr-msftMicrosoft Employee
Hi enzo2011,
If all this info was in one file, then I would have no problems, so I emphasize the fact that I got two diferent Excel worksheets.As we can import more than one Excel files as a single dataset in Power BI, you should be able to import both the Excel files into your pbix file with different table names, then do the comparison in this scenario.:smileyhappy:
So just open your existing pbix file with Power BI Desktop, click Get Data to import the new Excel file(B), then all data from both Excel files will be there.
Regards
Thanks v-ljerr-msft!
That could be an option, but I'm not very keen on adding new fields to my report every quarter from now on... And also I cant compare them row to row beacuse all rows are mixed and also some new ones are added. But still, I'll try to create a personalized column... or a new measure...
- Greg_Deckler
OK, in that circumstance, it sounds like you would need to do a Merge query as a Left or Right Anti join?
SabineOussiSkilled Sharer
Hi enzo2011
Did you find a solution for your issue?
I managed to reproduce your example and came up with this.When importing both of your worksheets, add a custom column to each of them, let's call it "IndexCol" having the values "A" and "B" for each table respectively.
Now that both tables are there with a "flag" for each one, go to edit queries and choose to append queries as new
Choose both tables, one as primary and one as secondary, apply and close.
In your report view, you will now see a new table having all of table A and table B elements.
Create a new calculated column as follows:Q4Files = CALCULATE(SUM(Append1[Files]), Append1[IndexCol]="B") - CALCULATE(SUM(Append1[Files]), Append1[IndexCol]="A")
Choose the table visual and add to it Process and Q4Files, make sure you are making your selection from the newly created table Append1 and that Q4Files is groupes as sum.You should get the following
Tell me how it works!
EDIT: if you have unique process values in both tables, why don't you create a 1:1 relationship between both worksheets on the process. Then create a calculated column on Table2 as follows: Q4Files = Table2[Files]-RELATED(Table1[Files])
