cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
_ai_ Frequent Visitor
Frequent Visitor

Return missing strings based on 2 conditions from two columns in the same table

Hi everyone, I was trying to solve this by looking for similar problems here but couldn't quite figure it out, some solutions involved creating a second table and use UNION/EXCEPT to show missing entries but it didn't quite work out. Maybe someone can point me into the right direction... The data: 200MB worth of rows...

 

Table1 Columns: String | Category | Type

 

There are 13 categories, each "String" needs to exist in each category with Category1 being the baseline, e.g. if the entry says "ABCD", "Cat1" then all other Cat2-13 need to say "ABCD". Sometimes a string would be missing or be misspelled for a certain category. e.g.

 

ABCD | Category1 | Type

ABCD | Category2 | Type

ABCD | Category3 | Type

...

ABCC | Category13 | Type

 

Type is the same across all categories/strings. There are a lot of types so I currently just filter for one type where I need to do this check. I would like to have a table returned that just shows the missing data (compared to Cat1 entries as baseline) in the below format, e.g. assuming ABCD is missing for CAT5 and is misspelled for Cat13

 

String | Category | Type | Error

ABCD | Cat5 | Type | Missing

ABCC | Cat13 | Type | Misspelled

 

Can I do this with just one table? If I create a second table with just the Cat1 baseline string values for type, each string will be unique. But since this would create another huge table I was wondering if this can be avoided. Thanks for reading!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Return missing strings based on 2 conditions from two columns in the same table

Hi @_ai_,

 

It seems that you want to return the missing strings in one table.

 

However, it may be impossible to return the missing String without comparison in power bI currently. You should create another table to have a comparison to figure out the missing string. 

 

You could create the temp table in a measure but it also have an effect for your calculation.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
_ai_ Frequent Visitor
Frequent Visitor

Re: Return missing strings based on 2 conditions from two columns in the same table

I use the matrix visual in the end and used count of type as value and count as column and string as rows. It at least lets me filter for those instances where a string is not occuring 13 times wihtout having to add any tables.

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,962)