New Bug Encountered: Minus LIST("") removes Duplicates in a List

Please consider the results in the Expression Assistant:

LIST(1,2,2,3) = 1 , 2 , 2 , 3

LIST(1,2,2,3) - LIST("") = 1 , 2 , 3

- LIST("") is meant to remove empty entries. But not to remove duplicates.

Solved Solved
0 9 563
1 ACCEPTED SOLUTION

@Fabian I had another thought. I think I thought of a really annoying way to do it. As long as the list isnโ€™t huge, I think this should work decently.

SPLIT(TRIM(SUBSTITUTE(CONCATENATE([YourList])), ", ", ""))," ")

Try playing around with that.

View solution in original post

9 REPLIES 9

Actuallyโ€ฆ it should remove duplicates as well. Though you can use UNIQUE(โ€ฆ) as well for duplicates.

So how could one remove blanks in a list, but not remove duplicates?

@Fabian - If the list you have is something along the lines of {1, 2, 2, 3, โ€œโ€, โ€œโ€}, I am pretty sure I have used -{null} at the end and been able to remove empties no problem. So try that.

But, fair warning, I think I have it set up to grab lookup values and if they donโ€™t exist to put in a null value so it doesnโ€™t generate any errors, but I think it should still work for you.

Thank you Dave,
I dinโ€™t know that we can use -{null] in expressions.
Just tried it out. Itโ€™s working, but it hat the same side effect as -LIST(""). It removes also duplicates.

Dang. Well, it was worth the shot.
And yes, I believe anywhere you could use โ€œโ€ you can instead use null, which may help improve readability. Not sure if it could possibly create errors elsewhere though.

@Fabian I had another thought. I think I thought of a really annoying way to do it. As long as the list isnโ€™t huge, I think this should work decently.

SPLIT(TRIM(SUBSTITUTE(CONCATENATE([YourList])), ", ", ""))," ")

Try playing around with that.

@Bahbus Thank you very much Dave. Thatโ€™s a solution

Thanks also to @Heino_Matthee and @Jervz.
But in my real case Iโ€™m extracting a list of test fragments (not numbers) from OCRTEXT().

Try Select(YourTable[column], true) - Select(YourTable[column], ISBLANK(column))

Jervz
Participant III

Hi Fabian, try this
EXTRACTNUMBERS(CONCATENATE([YourList]))

Top Labels in this Space