write power query or DAX script to compare without case sensitivity (2) text string values while ignoring certain values within text string?

0
votes

So I have a task of comparing (2) columns in Power BI, they are both text type data sources

One Column            Second Column

ApPle_red 6pk         Apple
apple_green each      Apple
Apple_yellow_organic  Apple

is there a way to ignore "_red","_green","_yellow"

current method for case insensitive I am using is:

IF(One Column = Second Column, "true", "false")
2
powerbidaxpowerquerym
I know I could extract on delimiter, but this is a basic context for the data I'm looking at, there are many extractions or value replacements that I would have to do, I was wondering if there was a better way – konsama

2 Answers

2
votes

The following is a simple solution using your example.

Column = 
VAR aux = UPPER ( LEFT ( 'Table'[One Column]; 5 ) )
RETURN IF ( aux = UPPER ( 'Table'[Second Column] ), "True", "False" )

The following is a more complex and generic solution that compares the text before the "_" on the first column with the text on the second column. 

Column = 
VAR position = SEARCH("_",'Table'[One Column], 1, BLANK()) - 1
VAR aux = UPPER(LEFT('Table'[One Column], position))

Return IF( aux = UPPER('Table'[Second Column]), "True", "False" )

Hope it helps you

2
votes

In the query editor, you could check if the first column starts with the string in the second column (after converting both to the same case):

Text.StartsWith(Text.Lower([One Column]), Text.Lower([Second Column]))

The same logic in DAX would be like this:

LEFT('Table'[One Column], LEN('Table'[Second Column])) = 'Table'[Second Column]