Comparing Values With Out of Order Data

[Scott0524]

Hello, is there any way to compare two tables with out of order data? For example, I would like to search for the value of data in set A and compare that to the same value of the same data of set B. This would be for very large data sets, and I would not like to correct each row that may be out of order. Example of row 2 below has data in set A that actually needs to be compared row 3 in set B. 

Set A	Set A Values		Set B	Set B Values		Difference
158427171	21		158427171	20		1
158427173	17365		158427172	126889		-109524
158427172	128462		158427173	15422		113040
158427174	1895		158427174	1854		41
158427175	148421		158427175	144892		3529
158427176	151282		158427176	147322		3960
158427177	116		158427177	113		3

[Anonymous · Accepted solution]

Hi @Scott0524 ,

Consider use  merge query in powerquery:

All steps and the output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY67EQAhCAV7ITY4kJ+1OPbfxokGB5cwzLLwmBNQnMnQEBpQlAIeWO1zeoytqySNoid1H0W92FkpuWdfmKioHNRHPnqRcPGOwLvPX17IPupNPcp+IMcfyNZ/+RYYNYmXdFjrBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Set A" = _t, #"Set A Values" = _t, #"Set B" = _t, #" Set B Values" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Set A", Int64.Type}, {"Set A Values", Int64.Type}, {"Set B", Int64.Type}, {" Set B Values", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Set A"},#"Changed Type",{"Set B"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {" Set B Values"}, {"NewColumn. Set B Values"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Difference", each [#"Set A Values"] - [#"NewColumn. Set B Values"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NewColumn. Set B Values"})
in
    #"Removed Columns"

 This is just a basic example and might need some modifications based on your specific requirements.

2. Please create a new calculated column.

Difference = 
VAR _A = 'Table'[Set A]
VAR _RESULT = 'Table'[Set A Values] - CALCULATE(MAX('Table'[Set B Values]),FILTER(ALL('Table'),'Table'[Set B]=_A))
RETURN
_RESULT

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Scott0524]

Set A		Set A Values		Set B		Set B Values		Difference
158427171		21		158427171		20		1
158427173		17365		158427172		126889		-109524
158427172		128462		158427173		15422		113040
158427174		1895		158427174		1854		41
158427175		148421		158427175		144892		3529
158427176		151282		158427176		147322		3960
158427177		116		158427177		113		3

[gmsamborn]

Hi @Scott0524 

In Power Query, I split the table into 2 tables and then...

If you create a one-to-one relationship between 'Set A'[Set A] and 'Set B'[Set B] then following measure seems to work.

Difference = 
VAR _A_val = SELECTEDVALUE( 'Set A'[Set A Values] )
VAR _B_val = MAX( 'Set B'[Set B Values] )
RETURN
    _A_val - _B_val    

Compare values.pbix

[Scott0524]

Thanks, When you "Create a one-to-one relationship" are you just sorting from smallest to largest to get them to be on the same row? This would work if all the numbers showed up in each set, but where I have an issue and where they get on different rows is when we have a number that shows up in Set A but not in Set B or vis versa, so the numbers get shifted off by one row. For example below, 158427172 is missing from Set B and the values get off. 

Set A		Set A Values		Set B		Set B Values		Difference
158427171		21		158427171		20		1
158427173		17365		158427173		15422		1943
158427172		128462		158427174		1854		126608
158427174		1895		158427175		144892		-142997
158427175		148421		158427176		147322		1099
158427176		151282		158427177		113		151169
158427177		116						116

[gmsamborn]

Is either of [Set A] or [Set B] a complete list?  In my example, I guess I'm making the assumption that [Set A] is a complete list.

If neither are complete, this could be easily handled by adding a 'Set' dimension.

[gmsamborn]

@Scott0524 

The order doesn't matter.  It's just a matter of if a match is found if that makes sense.

I changed the measure so that in the case of no match,a blank is returned.

Also, on the second page there is a table displaying the results.

Compare values - 2.pbix

I hope I'm understanding the requirement and this makes sense.  If not, maybe we'll have to try again.

[Scott0524]

I really appreciate the help. I am new to PowerBI. I tried to recreate what you did with some sample data, but I am unable to get it to add a Blank where it does not match. Please see attached photo. 

[gmsamborn]

Hi @Scott0524 

I can see by your latest screen-capture that you are trying to accomplish this with the data all in the original table.

I think it would be much easier to work with 2 separate tables (or even with a 3rd table: DimSet).'

Hopefully someone else can help you come up with a solution using a single table.