Forum Discussion
Saxon10
Post Prodigy
Post ProdigyConcat based on the column text
Hi, In data table I have following columns are item and sales code. The sales code contain number and item code contain number and text. I am trying ignore the following sales codes (<> fr...
The key here is the EXCEPT function to eliminate the stuff you don't want.
CONCOR = VAR Blacklist = { 9005, 9006, 9007, 9008, 9009, 9010, BLANK () } VAR Fulllist = CALCULATETABLE ( VALUES ( 'DATA (2)'[SALES CODE] ), ALLEXCEPT ( 'DATA (2)', 'DATA (2)'[ITEM] ) ) RETURN IF ( 'DATA (2)'[SALES CODE] IN Blacklist, BLANK (), CONCATENATEX ( EXCEPT ( Fulllist, Blacklist ), 'DATA (2)'[SALES CODE], "," ) )You can also write the Blacklist as UNION ( GENERATESERIES ( 9005, 9010 ), { BLANK () } ), which can easily extend to much larger ranges of values.
AlB
Community Champion
Community ChampionHi Saxon10
Watch out. The description is inconsistent with the desired result (9011). Try this:
New Col =
VAR exclude_ =
UNION ( GENERATESERIES ( 9005, 9010 ), ROW ( "Value", BLANK () ) )
VAR auxT_ =
FILTER (
CALCULATETABLE (
DISTINCT ( Table1[SALES CODE] ),
ALLEXCEPT ( Table1, Table1[ITEM] )
),
NOT Table1[SALES CODE] IN exclude_
)
RETURN
IF (
NOT Table1[SALES CODE] IN exclude_,
CONCATENATEX ( auxT_, Table1[SALES CODE], ",", Table1[SALES CODE], ASC )
)
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
AlexisOlson
Super User
Super UserYes, Saxon10 watch out for the discrepancies. In addition to the row with 9011, the bottom row in your post also does not match your description.
Saxon10Hi,
Thanks for your reply again.
Sorry I prepared the desired result manually that's the reason it went wrong. Thanks for address the discrepancies.