Compare two text columns and write the difference in the third column in PowerQuery

[LP280388]

Hi Team,

I have columns as below (excluding last column which the output Im trying to fetch) : 

Emp ID	Mgr ID	Employee States	Manager States	States Missing
G1	G0	A, B, C, D	A, B, D	C
G2	G0	X, Y, Z, E, F	Y, Z, F	X, E

Im trying to add a new column at the end "States Missing" with the values that are not present in Manager states compared to Employee states.  Im trying to do this powerquery.

Kindly help

[ryan_mayu · Accepted solution]

@LP280388 

you can try this to create a new column in PQ

= Table.AddColumn(#"Changed Type", "Custom", each
[a=Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.Remove([Employee States]," ")),
b=Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.Remove([Manager States]," ")),
c=List.Difference(a,b),
d=Text.Combine(c,",")
][d])

[ryan_mayu · Accepted solution]

@LP280388 

you can try this to create a new column in PQ

= Table.AddColumn(#"Changed Type", "Custom", each
[a=Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.Remove([Employee States]," ")),
b=Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.Remove([Manager States]," ")),
c=List.Difference(a,b),
d=Text.Combine(c,",")
][d])

[LP280388]

@ryan_mayu , Thank you. It did work with the sample data I provided. Thanks for the same. 

In my original data i have company names with space included like the below.  May I know how can i modify this query to suit this.

Employee States

Am ciat, Brian be, Ceat kod, Doug fen

[ryan_mayu · Accepted solution]

@LP280388 

you can try this

= Table.AddColumn(Source, "Custom", each
[a=Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.Trim([Employee States]," ")),
b=Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.Trim([Manager States]," ")),
c=List.Difference(a,b),
d=Text.Combine(c,",")
][d])