Help comparing dates between tables

[rdops]

Hi there, I am hoping someone can help me with this. Have spent hours and hours looking through the solutions posted and cant figure out.

I have two different systems for people to enter timesheet. Lets call them A and B. Once a month I do a leave reconciliation and need to check that the leave was entered on both systems for the same date. 

As shown below what happens is Mary might enter annual leave on 1st of May on system A but not on System B. So when I calculate the difference between both systems, Mary will be showing -2 but I dont know which days are missing. Note that we will have entries for the entire year so Mary will have several entries per year. I can get the graphic representation showing tha on system A she has 4 days entered, and on System B she has 2, but I can't figure out which date is missing on System B.

Also important to note that the date on System B is mandatory, i.e., people must have entries on that system. so I need a way to show to people which days that has been imput on System A that was not entered on System B. 

Hoping you can help.

[AllisonKennedy]

@rdops  try this DAX as a new TABLE from the modeling tab in the report:

Table = EXCEPT(SUMMARIZE(SourceA,SourceA[Name],SourceA[Start date],SourceA[Leave type], SourceA[Hours]),SUMMARIZE(SourceB,SourceB[Name],SourceB[Start date],SourceB[Leave type], SourceB[Hours]))

[rdops]

Hi Allison, I did this but it shows exactly the same thing that is on Source A. I mean, this table displays exactly the same as if I had a table for Sorce A (without any DAX).

[AllisonKennedy]

@rdops  What the below DAX function does is check each of the columns listed in System A for a matching value in System B. If the employee name or leave type are spelled differently, then it won't find a match in System B and will return that value. If you're getting the same values in the DAX as in System A, then that means it has found 0 matching rows between the two tables. This could be because your column order is different, or slight spelling, or even something as small as one System has a space after the name while the other system doesn't. 

Missing Hours =
EXCEPT (
SUMMARIZE (
'System A',
'System A'[Name],
'System A'[Start date],
'System A'[Leave type],
'System A'[Hours]
),
SUMMARIZE (
'System B',
'System B'[Name],
'System B'[Start date],
'System B'[Leave type],
'System B'[Hours]
)
)

Here's what I get when I use the DAX: 

Note if you want to filter the Missing Hours table by staff member, you'll need either a separate staff table with each staff member listed exactly once (preferred solution) or need to use the Name from the MissingHours summarized table. 

I still think you might be better off doing this inside the Power Query Editor, which would require you to create a unique key column for each system that can be used to find a match or not. Try this link to see if it helps explain a bit about how you can do that: https://radacad.com/choose-the-right-merge-join-type-in-power-bi

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[rdops]

OMG Allison!!!! It worked!!! It finally worked!!

The only thing I need to figure out now is how to resolve the names discrepancies between the two systems (eg, we have one name William Scott on system A and Will Scott on System B, in this case obvs William wont appear. Other than that, is f....king working.

I cannot thank you enough!!!!!

[AllisonKennedy]

Yay! So glad to hear you finally got it sorted. @rdops I knew you could do it!

Go and take a much deserved break, and when you're ready to tackle the names, come back and let us know how you get on. Here's a start idea for you:

To reconcile differences in names between the two systems, how many are there? If it's just a few you can do a Replace Values in the query editor. 

If you have many, I would recommend creating yourself a 'mapping' table of sorts to reconcile the name differences between the two systems: 

NameA	NameB
William Scott	Will Scott
F Smith	Fred Smith
M Jane	Mary Jane

Make sure NameA column has no duplicates. Then merge this mapping table into SysA table as a Left Outer join using Name column from the System A table and NameA from the mapping table. Click the arrow on the Mapping column to expand and select the NameB column.  Close and Apply. Then you just need to replace the NameB part in the DAX.  

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[rdops]

and I am officially done with this. Just cant make it work. Nothing that I do works. I cannot even look at this report anymore.

Also for the records, the number of columns and fields are different on each table so is not as straight forward. Anyway, I am done. 

Thanks everyone for all your help.

[AllisonKennedy]

@rdops 

Here's one more photo showing the sample with the other columns for reference: 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Hi @rdops ,

What's your expected result?

Not sure if i understand you correctly, but i used EXCEPT() function and below is the result.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[rdops]

Jay, tried yours but didnt work because the two systems are not as clean/simple as the two examples below.

I have about 20 different fields on each table/system. Is just that I only need the missing date in system B.

I used the formular you posted and got an error; I assume I need to tell the tool whych column on each table I want to compare.

I tried the exceptx(filtered option and got nowhere.

[rdops]

Thanks for your erply V-Jay. What I want is to know which dates are in system A but not in System B, which is exactly what you got there. Not sure why I cant make it work. Will try it again now using your except formula.

Thanks again.

Cheers 

[Anonymous]

Hi @rdops ,

Click the New Table feature under Modeling then paste the formula to the input box, that should work.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[AllisonKennedy]

A couple of things you can try:

Either do a MERGE in the Power Query Editor and choose LEFT ANTI as the Join Kind. If you Merge as new table, this will give you a 3rd table that will show the values in A that are not in B. Before you do this you will need to create a new merged column that has all the data that should be the same in each table, so in your case all 4 columns displayed. 

Or you could use dimension DATE, Employee and Leave Type tables in the Power BI report that can link the two tables and try to reconcile that way by using the columns from the dimension tables and create a measure that calculates the difference in hours between source A and B. Add this as a filter to the visualization and filter for when the difference is not equal to 0.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[rdops]

Thanks for your reply, Allison.

I tried this solution already but I must be doing something wrong as I dont see the result that I want. I am fairly new to Power BI and have no programming background so most of the times I just copy and paste the formulas that are posted here and if there is one step not described I will miss and therefor the solution wont work. I created the column with the employee's name, which is common to both tables, still didn't work. 

Would you mind elaborating a bit more on how to use dimension Date?. I already have the calculated hours difference between the two tables. Thanks a lot.

[AllisonKennedy]

@rdops  If I understand you correctly, you've calculated the hours difference between two tables based on name only. You need to base this on Name AND Date (and type too I think?)

So my Power Query Editor solution (similar to @Greg_Deckler 's solution but using Power Query instead of DAX): 

1. Click on Transform Data to open the Power Query Editor.
2. Select Sample A and use the Shift key to select all four columns. Click Add Column tab in the ribbon and choose Merge.
3. Repeate Step 2 with Sample B.
4. In the Home tab in the ribbon, select the down arrow near Merge Queries and choose Merge Queries as New. 
5. Select the Sample A and Sample B for the tables (order does matter, the top table should be the one that has all the results and the bottom table should be the one that only has some of the results)
6. Click on the 'Merged' column in both.
7. Choose Left Anti as the Join Kind. This will take the top table (Sample A) and remove any rows from it that are also in Sample B, but keep everything else. (see this post for an example: https://www.poweredsolutions.co/2019/01/10/merge-operations-in-power-bi-power-query-part-3-left-anti...) 
8. Click OK. 
9. You'll be left with a table that contains a funny looking column full of 'table'. You can delete that column and load this to the report. 

For my DimDate solution: 

1. Create a DimDate table. Here's one option for how to do that: https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
2. Do you already have an Employee table and a Leave Type table? If not, you should now that you are working with comparing two source tables. You can do this in Excel by just creating a simple table that has each employee listed exactly once, and another table that has each leave type listed exactly once. 
3. Load all 5 tables into Power BI (DimDate, SourceA, SourceB, Employee, LeaveType)
4. Check the relationships:
   1. Relate DimDate to SourceA using [Date] column.
   2. Relate Employee to SourceA using [Name]
   3. Relate LeaveType to SourceA using [LeaveType]
   4. Relate DimDate to SourceB using [Date] column.
   5. Relate Employee to SourceB using [Name]
   6. Relate LeaveType to SourceB using [LeaveType]
   7. All relationships should be 1 to many.
5. Create a few MEASURES:
   1. SouceATotalHours=SUM(SourceA[Hours])
   2. SourceBTotalHours=SUM(SourceB[Hours])
   3. DifferenceHours=[SourceATotalHours]-[SourceBTotalHours]
6. Last step is to get the visualization right, because MEASURES change value depending on the context of the visualization they're place in. So:
   1. Put Employee[Name], LeaveType[Type], DimDate[Date] into Rows for a Matrix visualization.
   2. Put DifferenceHours in the values for that same matrix.
   3. You should only see rows that are not equal to 0. Note if the number hours is negative: this method will also display rows that are only in SourceB but not in SourceA if that somehow were to happen (though I believe you said that's not possible).

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[rdops]

Thank you so much for that, Allison.

Really appreciate you taking the time to being so detailed.

I tried the merge, but still cant make it work. Spent the entire morning trying 😞 

I looked at DimDate solution and is way too advanced for me to even attempt.

I will stick to my basic way of visualising it (I just have two separate tables (graphs) one for each system with a single slicer, by name) and will look for what is missing. This is super frustrating for me but one has to acknowledge its limitations. I dont think i will ever be a power bi Advanced user, that's the sad truth (pitty because I love the tool).

Thank you so much and I iam sure your post will help many other people with the same query.

Cheers

[Greg_Deckler]

Use CONCATENATEX(EXCEPT( <filtered dates from A>, <filtered dates from B>),[Date],",")

[rdops]

Thanks Greg, I will try this one now to see if it works. Will report back (though not sure I understand how the filtered dates work but will give it a go). Thanks

[Greg_Deckler]

@rdops - If you have the names of the person in a visual then you could do something like:

VAR __Name = MAX('Table'[Name])

CONCATENATEX(

  EXCEPT(

    SELECTCOLUMNS(FILTER('Table1',[Name]=__Name),"Date",[Date]))

    SELECTCOLUMNS(FILTER('Table2',[Name]=__Name),"Date",[Date]))

  ),

  [Date],

  ","

)

So, basically, get me all the dates from table 1 and take away all of the dates from table 2 so now the ones left are the missing ones.

[rdops]

Thanks Greg.

I tried but couldnt make it work. I guess I am just to jr for the tool.

Thanks anyway