cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
enzo2011
Frequent Visitor

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

1 ACCEPTED SOLUTION
SabineOussi
Skilled Sharer
Skilled 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

Untitled.png

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

Capture.PNG

 

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

View solution in original post

7 REPLIES 7
SabineOussi
Skilled Sharer
Skilled 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

Untitled.png

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

Capture.PNG

 

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

v-ljerr-msft
Microsoft
Microsoft

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.Smiley Happy 

 

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... 

 

 

OK, in that circumstance, it sounds like you would need to do a Merge query as a Left or Right Anti join?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Let me show you a simplified example...

Captura.PNG

 

So we have two diferent Excel Files. A and B.

 

If I wanna know the generated files for each process during the last quarter of 2016 we should subtract one table from the other one. Therefore, as an example, 52 new files have been generated this last quarter for the process RE001, and also the new process (TQ003). As you can see they are not in order. My question is if there's a way to do this operation inside Power BI. Any solution that helps me find the new files and processes generated from a new file that I will get every quarter.

 

Thank you

 

 

 

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.