Compare lines between excel files wiht Power BI

[DimD]

Hello community,

I hope you are well.
I have a small question.
I have two excel files that you will find attached.
And I'm looking to compare them and know:
-the one that is no longer present
-the one that has been modified
-the one that has been added.

Every day I will get a new file.
I have followed these links : 

https://community.powerbi.com/t5/Desktop/Compare-two-excel-files-in-power-bi-to-get-an-specific/m-p/...
https://community.powerbi.com/t5/Desktop/Compare-data-from-two-files-and-fill-a-new-column/m-p/11372...
https://community.powerbi.com/t5/Community-Blog/Compare-2-Files-Line-by-Line-in-Power-Query/ba-p/905...

but I can't get what I want.
Can you help me?

Thanks in advance

https://we.tl/t-wVHhetYEK1 

[DimD · Accepted solution]

Hello, 

I  found solution. Not the best one but it works for me. 

Will works on it later to improve the solution. 

- I put all files in a folder and I choose it as source

- I create a date table. the relationship is inactive, because I have some issue --> Continue to work on it

- Create several values (is added values, is deleted value, Is in current file, is in previous, is modified, is using relationship) here is a sample of measure

Then I make measure who count row 

added, deleted, current .. like this

Use measure as filters and I have my solution

Thank you for your help.

[v-chenwuz-msft]

Hi @DimD ,

I suggest you get the data from the folder. And sort the files in the folder by name or creation date, i.e. make the new excel the first row of the table and the last excel the second row. At this point when you reference the data it will always get the newest and last one in the folder.

Then load these two tables.

Use the following measures in filter pane of table visuals and set it show itmes which is 1.

the one that has been added =
IF ( SELECTEDVALUE ( new[IMEI] ) IN VALUES ( last[IMEI] ), BLANK (), 1 )

the one that has been modified =
VAR _last =
    CALCULATE (
        SELECTEDVALUE ( last[Modification date] ),
        FILTER ( 'last', [IMEI] = SELECTEDVALUE ( new[IMEI] ) )
    )
RETURN
    IF ( SELECTEDVALUE ( new[Modification date] ) <> _last, 1 )

the one that is no longer present =
IF ( SELECTEDVALUE ( last[IMEI] ) IN VALUES ( new[IMEI] ), BLANK (), 1 )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DimD]

Hello @v-chenwuz-msft , 

First thank you for your answer. 

I have 3 questions, please : 

- May I have your xls files that you use in your PBIX ?

- Now if I have every days the same extract files in a folder. The name of the files contain the date (ex: 20220722_Ticket; 20220721_Tickets (same structure)  How to do the same steps ? I choose Folder as source ?

Then I can have the file date. And in this case can I know the date when the ticket is added, removed or modified based on this date ?

Thank you in advance for your answer 🙂