Merging between Two excel files

[Leozin28]

Hey gurus, hope you all are fine!

I had some trouble with merging two different yet somehow same Excel files into my Power Bi and hope someone could help me 🙂

To start, both files (let´s call them A and B) had 2 columns in bonding, being "Client" and "Manager". 

You could say that File A is a sum-up from file B, when talking about 40 Clients both have.

The first sheet, Excel A, has around 40 rows with 40 uniques Clients, while they are distributed in 2 Managers.

The second xlsx, however, has around 2500 rows with those 40 same Clients + 20 new ones, being split between those 2 Managers as well.

Therefore, I tried to merge AND append those files in PowerBi, yet it didn´t work at all. I had about 4 different errors pop-ups and everytime I solved one, there was another right around the corner waiting to pop.

My main goal with this PowerBi is, in one single dash, have to select whether I want to present file A data (sum-up, kind of Total) or if I want to go specific, selecting File B data (much richer and more complete info).

Also tried to study DAX to combine those "same" columns, but found it way harder and don´t reckon it would work.

I am looking forward for your tips to trying to make it work and/or give me a solution!
If I may be going into a whole complex idea and there is one way easier, will be happy to hear and learn!!

Ty in advance! 

[v-jingzhan-msft]

Hi @Leozin28 

Not sure how you want to display data in the report. Let's take a table visual as an example. If you want the table to display only data from file A or only data from file B, you can add two table visuals to hold those data separately. Then use bookmarks and buttons to hide/show a table at a time. Bookmarks and Buttons: Making Power BI Charts Even More Interactive - RADACAD

If you want a more complex result, we may need to create some DAX calculations to get the result. We may also need to transform the data in advance before doing any calculation. However in this scenario it's difficult to give a detailed example as it may be affected by several conditions. Can you provide some sample data and the expected output so that we can try to provide a more detailed solution? 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Leozin28]

Hey @v-jingzhan-msft 

Thank you for reaching me quickly. Sure, I´ll try to explain better the situation and what I desire my outcome to be.

Just like before, the first sheet will be called A and second will be file B. As you may see, they are different having 2 categories in common (Clients e Officer).

A:

Category

Product

Client

Officer

Date

PL

B:

Client

Officer

AuC

Clients in file A are around 40. Those exactly 40 clients are in file B as well, with an additional 20 new clients. 

My main goal with this dash is: 

In ONE dash (not a dash per sheet), with a Slicer, I should choose the Officer name. Then it should come with his own clients list. 

After that, I reckon it is easier. I will create 5 cards to present data (5 remaining categories from both sheets)  based in my choice of client.

The problem with all this is feeding the first slicer to dictate the whole dash itself. The fact that I could just use some Excel like -> Sum (File A + File B) and it would merge those files, could be way easier.

Anyway, will be looking forward to your reply! 
Thank you!

[lbendlin]

The best merge is one that you can avoid.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Leozin28]

Hey @lbendlin 

Thank you for reaching me quickly. Sure, I´ll try to explain better the situation and what I desire my outcome to be.

Just like before, the first sheet will be called A and second will be file B. As you may see, they are different having 2 categories in common (Clients e Officer).

A:

Category

Product

Client

Officer

Date

PL

B:

Client

Officer

AuC

Clients in file A are around 40. Those exactly 40 clients are in file B as well, with an additional 20 new clients. 

My main goal with this dash is: 

In ONE dash (not a dash per sheet), with a Slicer, I should choose the Officer name. Then it should come with his own clients list. 

After that, I reckon it is easier. I will create 5 cards to present data (5 remaining categories from both sheets)  based in my choice of client.

The problem with all this is feeding the first slicer to dictate the whole dash itself. The fact that I could just use some Excel like -> Sum (File A + File B) and it would merge those files, could be way easier.

Anyway, will be looking forward to your reply! 
Thank you!

[lbendlin]

sorry, cannot help you without usable sample data.