Comma separated column where/If condition

[sdsai777]

Hello All,

I have a unique ID and a CONCAT column with comma seperated value and Item ID. Below is the data set and the result expected.

ID	CONCAT column	Item ID
1	100, 200, 300, 400 , 500	100, 300
2	700, 800, 100, 200, 300	100
3	1000, 2000, 3000, 4000	500
8	1000	100
6	1000, 2000, 200, 8000	8000
4	100, 500	1000, 100

IF the item ID has any one value in the CONCAT column then Y else N.

The above is just a sample data and the list of CONCAT columns is very big. I tried to split the column via comma it gave me like 100 extra columns in which I was not able to do a IF/where condition.

Below is the expected result

ID	CONCAT column	Item ID	Indicator
1	100, 200, 300, 400 , 500	100, 300	Y
2	700, 800, 100, 200, 300	100	Y
3	1000, 2000, 3000, 4000	500	N
8	1000	100	N
6	1000, 2000, 200, 8000	8000	Y
4	100, 500	1000, 100	Y

Thanks for looking at this and let me know if there is any solution.

[v-jayw-msft]

Hi @sdsai777 ,

Check this blog.

https://dax.tips/2019/07/05/dax-pivot-text-into-a-list-of-words/ 

List the values for both CONCAT column and Item ID then you may compare them.

Best Regards,

Jay

[mahoneypat]

Looking up a single ID in the Concat List is straight forward with SEARCH, but it is trickier to find the commas, parse out the characters before/after it, remove the spaces and then SEARCH for those is the Concat column.

Pat

[FrankAT]

Hi @sdsai777 ,

take a look at the following link:

http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[sdsai777]

Thanks for the reply Frank, but I am looking at a where/IF condition without splitting the column.

Thanks

[mahoneypat]

In the query editor, you can add a custom column and put this formula in the pop-up box.

let

concatlist = List.Transform(Text.Split([CONCAT column], ","), each Text.Trim(_)),
IDlist = List.Transform(Text.Split([Item ID], ","), each Text.Trim(_))
in
if List.Count(List.Intersect({concatlist, IDlist}))>0 then "Y" else "N"

The List.Transform function is needed as sometimes you have an extra space between list items.  This removes the spaces if there.

Pat

[sdsai777]

Thanks for the reply Pat. This table is derived using DAX statement in Power BI desktop and I am unable to edit it in Power Query. Is there any way to do using DAX statement?