Compare 2 tables easily - how do you do ?

LaurentZ - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 Put it in very general terms, the question is schematized by saying that having to "cross" two tables of 10 ^ 5 lines, 10 ^ 10 comparisons will have to be made in the worst case. All these comparisons take a certain amount of time, which can be different if you use different lookup or merge techniques or something else.

but if instead we could divide the problem into, say, 100 under problems of 10 ^ 3 elements, the comparisons would be 10 ^ 3 X 10 ^ 3 X 100. Therefore, we would have a time 100 times shorter, with the same technique, compared to the original time.

In this case, how could one divide the problem into subproblems?

We should have more information to find an effective solution.

For example, how many customers are there?

How many products are there?

####

another way would be to limit the search for the row corresponding to the current new row in the old table to a small subset of the rows instead of scrolling through all 100,000 rows for each search. If you manage to limit the search to a group of 1000 lines, you improve the search time by 100 times.

just to give an idea, but an effective solution strongly depends on the structure of the tables, you can sort the tables in increasing order with respect to the customer column and when you do the search you use Table.PositionOf to find the first (and only) occurrence of the row with the same customer and product. The next search is done starting from the previous position on a reduced table table.skip (tab, positionof); again you can limit the search towards the end of the table by stopping when records are found with customerIDold> customerIDnew.
in this way, for each search, a few hundred comparisons are made instead of 100000. In theory, obtaining a gain of 1000 times!?!?!?!?

Hi, LaurentZ 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Actual:

Forecast:

You may create a blank query and input the following codes to create a table for difference.

let
    Source = Table.AddColumn(Actual, "New Scenario", each "Difference"),
    #"Added Custom" = Table.AddColumn(Source, "New Quantity", each let customer=[Customer],product=[Product],
tab=Table.SelectRows(Forecast,each [Customer]=customer and [Product]=product)
in 
if Table.RowCount(tab)>0 then
[Quantity]-tab[Quantity]{0}
else [Quantity]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Scenario", "Quantity"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"New Scenario", "Customer", "Product", "New Quantity"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"New Scenario", "Scenario"}, {"New Quantity", "Quantity"}})
in
    #"Renamed Columns"

Then you may create another blank query for final result as below.

let
    #"Appended Query" = Table.Combine({ Actual, Forecast, New})
in
    #"Appended Query"

Best Regards

Allan

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

Hello LaurentZ 

here another approach by using Table.Join, expanding the result, making your calculation and reshaping it. 

After that join all 3 created tables

let
    New = #table
	(
		{"Scenario","Customer","Product","Quantity"},
		{
			{"Actuals","A","1","5"},	{"Actuals","B","1","5"},	{"Actuals","B","2","1"}
		}
	),
    Old = #table
	(
		{"Scenario","Customer","Product","Quantity"},
		{
			{"Forecast","A","1","3"},	{"Forecast","B","2","2"}
		}
	),
	Difference = 
	let 
		ToJoin = Table.NestedJoin(New, {"Customer", "Product"}, Old, {"Customer", "Product"}, "Old", JoinKind.FullOuter ),
    	#"Expanded Old" = Table.ExpandTableColumn(ToJoin, "Old", {"Scenario", "Customer", "Product", "Quantity"}, {"Old.Scenario", "Old.Customer", "Old.Product", "Old.Quantity"}),
    	#"Replaced Value" = Table.ReplaceValue(#"Expanded Old",null,"0",Replacer.ReplaceValue,Table.ColumnNames(#"Expanded Old")),
    	#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Number.From([Quantity])-Number.From([Old.Quantity])),
    	#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each "Difference"),
    	#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1", "Custom", "Customer", "Product"}),
    	#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom", "Quantity"}, {"Custom.1", "Scenario"}})
	in 
		#"Renamed Columns",
	Append = New & Old & Difference,
    #"Changed Type" = Table.TransformColumnTypes(Append,{{"Quantity", Int64.Type}})
in
    #"Changed Type"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy