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 compa...
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 )
)
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
Icon for Community Champion rankCommunity 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.

 

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous ,

    I created two sample tables:

     

    ip_address_rapid7

    b
    c
    d

     

    This is the result

    So the values "a", "e" and "f" are not in your second table called it_current.

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Another error, I used the following to create a new column in Table A:

      Missing from Rapid7 = LOOKUPVALUE(it_current[ip_address_rapid7],it_current[ip_address_rapid7], [ip_address])

      Error: The column 'CMDB_Active'[Missing from Rapid7] cannot be pushed to the remote data source and cannot be used in this scenario.

      • rsbin's avatar
        rsbin
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous ,

        I have never seen this error message before.  I did a quick google search using the phrase "cannot be pushed to the remote data source and cannot be used in this scenario".

        Are you using DirectQuery or a composite model?

        If this is the case, we may have to try something different.  Suggest you search the same and to see if you can find an answer.  I am stumped at the moment.