Compare two tables each month and add differences to new table

[Jocky]

Hi there,

I have a file every month showing all employees.  I want to be able to compare the differences in the file each month - for example, compare Feb with Jan, Mar with Feb, Apr with Mar, etc, then list all differences in a mastertable that gets appended each month with any new information from the latest comparison.

Ideally i would just drop the new file in a folder somewhere and powerquery would do the rest (eg have a "get data from file" option setup for wherever the two files it needs to compare will be".

Are there any options to do something like this?  I'm fine for comparing the two tables, just not sure if there is a way where every month the result could be added to a master table that builds up with data over time.

[jbwtp]

Hi @Jocky,

This is possible. In the simplest way using Merge on by file basis. However, actual view and complexity would depend on what do you mean by comparing files. If Apr file has a new person vs Mar file, it will be listed. Does it also need to show anyone who departured (i.e. present in Mar, but not in Apr)?

Would be great if you could put together a small sampe of how do you want the output table look like (just fields and data relevant to this question).

Thanks,

John

[Jocky]

Hi jbwtp,

Thanks for your reply.

If we imagine that we start with this data (which is a list of staff for January and February):

31 January

Name	Department
Bill	Finance
Bob	Marketing
Sally	Finance
Liz	Operations
Ron	Operations
Karen	Operations
Jack	Operations
Heather	Marketing
Emma	Marketing
Susan	Marketing
Theo	Operations

28 February

Name	Department
Bill	Finance
Bob	Marketing
Liz	Operations
Ron	Operations
Karen	Operations
Jack	Operations
Heather	Operations
Emma	Marketing
Susan	Marketing
Theo	Operations

Ultimately i want to be able to create a list of anybody who is no longer in the same department the following month (or has left).  This would capture that Sally left and Heather moved from Marketing to Operations and that it happened in February (ie the change is between the 31 January list and the 28 February list).  Example of result below.

Resulting table

Name	Department	Month
Sally	Finance	Feb
Heather	Marketing	Feb

Now getting to this stage i am absolutely fine with by using merge functions.  However, what i am looking to do is make this resulting list continue to develop on a monthly basis and as low maintanance as possible (eg just dropping the new CSV file somewhere.

So if March's file looked like this:

31 March

Name	Department
Bill	Finance
Bob	Marketing
Liz	Finance
Ron	Operations
Karen	Operations
Jack	Operations
Heather	Operations
Emma	Marketing
Susan	Marketing
Theo	Operations

It would pick up that Liz moved from Operations to Finance and the resulting table would continue to update like this:

Resulting table

Name	Department	Month
Sally	Finance	Feb
Heather	Marketing	Feb
Lis	Operations	Mar

Thanks

-Jocky

[jbwtp]

Hi @Jocky,

Try something like this. Could you please check the performance? I think this may be quite bad as files could be loaded several times.

In your version you will need to modify it to take your list of files (sorted in the right order) instead of the list of tables which I created to demonstrate the idea:

let
    Jan = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyVHSUXLLzEvMS05VitUBCuUnAUV8E4uyU0sy89LBYsGJOTmVaOp8MquAIv4FqUWJJZn5ecVgwaD8PExB78SiVCzCXonJ2ZiiHqmJJRmpRRhOcM3NTcR0V2lxYh6GaEhGaj6awbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Department = _t]),
    Feb = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc47CoAwEEXRvaR2FYIifhDULliMYdBBM5EkNq7eYOnYnguPp7XK6ThUpkpiYINqzhK5JUkHfsdIvL7W0p2sP9FDJMfhxcGxxAY8/nANZpdaIcQNvQyFtSBOjFcAFjpt6D4D8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Department = _t]),
    Mar = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyVHSUXLLzEvMS05VitUBCuUnAUV8E4uyU0sy89LBYj6ZVWiqgvLzgCL+BalFiSWZ+XnFYEHvxKJULMJeicnZmKIeqYklGalFmBKuubmJGC4ILi1OzMMQDclIzUczIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Department = _t]),
    #"This is your list of files" = Table.FromColumns({{"Jan", "Feb", "Mar"}, {Jan, Feb, Mar}}, type table [FileName = text, Content = table]),
    
    fAccumulate = (previousRow, currentRow)=> 
        let 
            #"Merged Queries" = Table.NestedJoin(previousRow[Content], {"Name", "Department"}, currentRow[Content], {"Name", "Department"}, "Content", JoinKind.LeftAnti),
            #"Added Custom" = Table.AddColumn(#"Merged Queries", "Month", each currentRow[FileName], type text),
            #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Department", "Month"})
        in #"Removed Other Columns",
    Decompose = Table.ToRecords(#"This is your list of files"),
    Process = List.Accumulate(Decompose, {{#table({},{}), Decompose{0}}}, (a, n)=> a & {{Table.Combine({List.Last(a){0}, fAccumulate(List.Last(a){1}, n)}), n}}),
    Output = List.Last(List.Zip(Process){0})
in Output

Kind regards,

John

[Jocky]

Thanks John.  I'll use this as something to work with and see where I get to.