Forum Discussion
Compare columns, INDEX and MATCH equivalent?
Ok, admitting defeat here, 3 days of PowerBi under my belt and some fierce Googling later i'm still at a wall.
I'm in the process of moving a legacy 'report' (report in the looses possible terms) from Excel to PowerBi, it's a relatively basic asset comparison report comparing data we have in ServiceNow (manually added) and data obtained from Intune (actively inventoried), end goal is to help maintain the quality of the ServiceNow Data.
I've got an initial visualisation cobbled together (below) and I've been looking to add an extra column after fields such as the device Name, Model, Serial number etc.. that simply has an entry of MATCH or MISMATCH
Historically this has been done in excel with INDEX and MATCH so wondering if there is an equivalent I can implement here, I've tried some IF statements and played with LOOKUPVALUE but I feel like i'm missing something fundamental in my understanding of how the data relates to each other.
The data sets I have are 'devices' (coming from Intune directly(in blue above)) and 'Page 1' (which is coming from an excel document exported from ServiceNow, (until I can get access to query it directly(in grey above)))
Datasets are linked on SerialNumber for the above but, for example, if i'm trying to match 'devices'[deviceName] and 'Page 1'[Asset tag] to see if the value in ServiceNow matches Intune I just get the same result for every entry.
As I say, just down to my understanding, possibly why my searches haven't helped as not 100% on what to look for.
Any nudges in the right direction are much appreciated!
(Also, apologies if the above makes less than no sense)
Hi Anonymous ,
You can add an index to the table in the power query, and then judge based on the index column.
Generally speaking, the relationship in PowerBI is limited to a single column. If you want to create a relationship between multiple columns in Power BI, we only need to merge the required columns together to create a new
Column, you can refer to the following blog for details: Create a relationship with multiple columns in Power BI | Dan KinsellaIf the problem is still not resolved, can you further clarify your needs, and provide test data (delete sensitive information) and expected results. I will answer for you as soon as possible.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-henryk-mstfCommunity Support
Hi Anonymous ,
You can add an index to the table in the power query, and then judge based on the index column.
Generally speaking, the relationship in PowerBI is limited to a single column. If you want to create a relationship between multiple columns in Power BI, we only need to merge the required columns together to create a new
Column, you can refer to the following blog for details: Create a relationship with multiple columns in Power BI | Dan KinsellaIf the problem is still not resolved, can you further clarify your needs, and provide test data (delete sensitive information) and expected results. I will answer for you as soon as possible.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.