Changes Between Reports - Complexity Management

[Anonymous]

Hello Everyone,

Here is the challenge I am facing. I have one headcount report (*xlsx) every month. Exactly the same columns, thousands of rows. 

What is the goal?

1. Compare records between the reports and see which employees are missing.

I can do that by merging tables with ANTI joins. So, for instance, I will see which employees are missing in the August report, but are included in the September report. And another way around - who is included in September, but missing in August.

I would like to see the output in two new tables, for example, if comparing September to August, 20190831_Leavers, and 20190831_Hires. 

I want this to be as automated as possible... but I am not sure how to do so. Ideally - pasting the file with August, September, October, etc. report to the folder and all the rest happens automatically... is the Python script good solution for achieving this?

2. Append all the records from all tables to the "Global" table with a new column showing the name of the original table (for instance 20190831_Leavers, 20190831_Hires).

Therefore, I would be able to quickly filter this column and show how many employees left YTD or each month, all the fluxes, etc.

thank you for help!

[Nathaniel_C]

Hi @Anonymous ,

Will attach Pbix in  a couple.  The crux was selecting the second month, and creating new table from the merge, and using anti.  This is all in Power Query so should be fully auto.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

let
    Source = Table.NestedJoin(September, {"Emp"}, August, {"Emp"}, "August", JoinKind.RightAnti),
    #"Expanded August" = Table.ExpandTableColumn(Source, "August", {"Emp", "ID"}, {"August.Emp", "August.ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded August",{"Emp", "ID"})
in
    #"Removed Columns"

[Nathaniel_C]

Hi @Anonymous ,

Here is the PBIX Hires and Fires
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C  - thank you for your help!

I am just wondering now... once I get October data (*xlsx), is there a smart way (automation) that will automatically create four new tables with:

* October New Hires (vs August and vs Sep)

* October New Fires (vs August and vs Sep)

Or do I need to manually create queries and adjust the M code?

thank you once again!