Compare two sets of data from the same source

[ruthpozuelo]

Hi,

An example, lets say I want to compare two sets of data from the same source, for example a bill of materials for a bike.

This data source changes when users update the data, but there are no flags such as date modified that can be imported for comparision.

For example week 1 the bill of materials look like this:

and week 2 when refreshed, the number of screws changed from 30 to 18:

I am using Chris Webb solution here: https://blog.crossjoin.co.uk/2014/01/27/comparing-columns-in-power-query/ to identify what changed between the tables by importing the data two times and reference the first table in the second to compare both,

My issue is that when I refresh the second table, the first table will refresh too! 😞

Is there a way to stop a table from refreshing with M, disable refresh on the first table or a better way to do this perhaps?

Thanks,

/Ruth

[asocorro · Accepted solution]

Here it is:

[ruthpozuelo]

Now, this is quite odd,

I stopped refresh on the first query and included it in the second query to compare both tables, the refreshed and unrefreshed one.

Query 1: unrefreshed

Query 2: refreshed

My surprise was that when I refresh the Query 2 (that includes Query 1), Query 1 is also refreshed inside query 2, But Query 1 remains unrefreshed..Perhaps useful in other cases but not for this case. 

Any ideas on how to disable refresh with M?

/Ruth

[asocorro]

How about doing the comparison in DAX?  One way would be to add a calculated column in the new table to do a lookup of the corresponding value in the original table and then compare them.

[ruthpozuelo]

Sorry for my Uber Late reply!

I was hoping to build a more automated solution, something like:

Click on refresh button, all queries are refreshed except for that table and then refresh that manually...sort of...

I already have a lookup in place, but it is not optimal...

Thanks for the support anyhow 🙂

[ruthpozuelo]

Of course, thanks!

/Ruth