Forum Discussion

Scott0524's avatar
Scott0524
New Member
2 years ago
Solved

Comparing Values With Out of Order Data

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 ASet A Values Set B Set B Values Difference
15842717121 15842717120 1
15842717317365 158427172126889 -109524
158427172128462 15842717315422 113040
1584271741895 1584271741854 41
158427175148421 158427175144892 3529
158427176151282 158427176147322 3960
158427177116 158427177113 3
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

8 Replies

  • Scott0524's avatar
    Scott0524
    New Member
    2 years ago
    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's avatar
      gmsamborn
      Super User
      2 years ago

      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's avatar
        Scott0524
        New Member
        2 years ago

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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