Forum Discussion
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
v-piga-msftResident Rockstar
Hi Anonymous,
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