Comparing Data in a row to check for compliance

[Nikkorasu]

Howdy All!

First time caller, long time listener... 🤣

I have a table [WinAndBIOSvers] that contains some scan information from computers.  One of those fields is the version of the BIOS on the workstation.  I have a separate table [BIOSversData] where I have identified the most recent version number of the BIOS and I am attempting to compare those values so I can tell which systems are deficient and need to be updated.

To accomplish this I have a column that compares the computer model with a matching column in the [BIOSversData] table.  It looks like this:

BIOS Reference = LOOKUPVALUE(BIOSversData[Current BIOS Version],BIOSversData[Computer Model],'WinAndBIOSvers'[Computer Model (groups)])

This was able to pull up the correct bios version for that machine and display it in the column for each device.

The problem I'm having now is trying to write an IF statement to compare the two versions and give a result.  When I build out the IF statement like below:

BIOS Compliance Check = IF('WinAndBIOSvers'[Computer BIOS Version]='WinAndBIOSvers'[BIOS Reference],"In Compliance","Not Compliant")

I get the error:

<ccon>A circular dependency was detected:  WinAndBIOSvers[BIOS Reference], WinAndBIOSvers[Column], WinAndBIOSvers[BIOS Reference].</ccon>

Any idea how to correct this?  I've spent the last several days trying to get these two tables to work together so I can let my technicians know what machines they need to update.

Any help would be appriciated.  Thanks.

[Nikkorasu]

Thank you for the idea, but I don't think this will work in Power Query unless I do some major retooling.

The gist of the problem comes from the computer models having different ways of being identified. 

For example the HP EliteDesk 800 G1 SFF has these descriptions for it:

Elite Desk 800 G1 SFF

HP EliteDesk 800 G1 SFF

C8N26AV

HP ELITE DESK 800 G1 SFF

And it goes on and on...

So what I have had to do is group the Computer Models into a group with their correct names so that they are all uniform.  In order for me to use the power query method, I'd then have to break all those names down in to the query which I haven't done, and would be painful to go back and have to do.  We have 96 different computer models out there and I had to go through and correct about half of them.

Are there any other ideas on how I might accomplish this?

[Anonymous]

Hi @Nikkorasu ,

If possible, try going through this problem in the PowerQuery Editor.

In 'WinAndBIOSvers' -> Merge Queries -> Expand column

Add conditional column

Result:

The PBIX file is attached for reference.

If I have misunderstood your needs or if the problem remains unresolved, please feel free to contact us.

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