Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing Two Different Datasets for missing values

Hi,

 

I have two different datasets/tables in Power BI. One that is a Power BI dataset and the other is a Google BigQuery direct query.

 

Lets call them table_a and table_b. I would like to compare what values in Column A in table_a are missing from Column A in table_b.

 

Any tips on the easiest way to go about doing this?

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    You can create a new calculated table 

    Missing From b =
EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) )

    Or you can create a measure, for example for a card visual 

    Missing From b =
CONCATENATEX (
    EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) ),
    [Column A],
    UNICHAR ( 10 )
)

13 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    You can create a new calculated table 

    Missing From b =
EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) )

    Or you can create a measure, for example for a card visual 

    Missing From b =
CONCATENATEX (
    EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) ),
    [Column A],
    UNICHAR ( 10 )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I used the first suggestion of creating a new calculated table and that worked, thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tired your first calculated table suggestion and got the following error:

       

      Error Message:
      MdxScript(Model) (59, 38) Calculation error in measure 'CMDB_Active'[Missing from b]: A table of multiple values was supplied where a single value was expected.

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Anonymous 
        For option 1 please make sure you are creating a calculated table not measure

        If you want to create a measure please use option 2

  • rsbin's avatar
    rsbin
    Community Champion
    4 years ago

    Anonymous 

    I think using the LOOKUPVALUE function should do the trick.

    Create a new Calculated Column in your table_a.  The syntax should be something like this:

     

    ColumnA_fromTable_b = LOOKUPVALUE( table_b[Column A],table_b[Column A], [Column A] )

     

    From Microsoft documentation:

     

    LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)

     

    Hope this works for you.

    Regards,

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I think the naming convention I used for the columns and tables is a bit confusing so let me use the actual names.

       

      Within the table named CMDB_Active and the column within that table called ip_address, I am trying to see what values are contained in that column that aren't in a separate table called it_current from the column ip_address_rapid7

       

      Let me know if that makes sense

      • rsbin's avatar
        rsbin
        Community Champion
        4 years ago

        Anonymous ,

        It would be helpful if you can you paste a small sample of data (just several records) from each table into a reply.  Paste it as a table, not a picture.