Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jocky
Frequent Visitor

Compare two tables each month and add differences to new table

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.

4 REPLIES 4
jbwtp
Memorable Member
Memorable Member

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
Frequent Visitor

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

NameDepartment
BillFinance
BobMarketing
SallyFinance
LizOperations
RonOperations
KarenOperations
JackOperations
HeatherMarketing
EmmaMarketing
SusanMarketing
TheoOperations

 

28 February

NameDepartment
BillFinance
BobMarketing
LizOperations
RonOperations
KarenOperations
JackOperations
HeatherOperations
EmmaMarketing
SusanMarketing
TheoOperations

 

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

NameDepartmentMonth
SallyFinanceFeb
HeatherMarketingFeb

 

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

NameDepartment
BillFinance
BobMarketing
LizFinance
RonOperations
KarenOperations
JackOperations
HeatherOperations
EmmaMarketing
SusanMarketing
TheoOperations

 

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

 

Resulting table

NameDepartmentMonth
SallyFinanceFeb
HeatherMarketingFeb
LisOperationsMar

 

Thanks

-Jocky

jbwtp
Memorable Member
Memorable Member

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
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors