compare two columns which are in the form of lists

[Anonymous]

Hi all,

I need to compare two columns which are in the form of lists. I am aware that I can achieve this by expanding and unpivoting (similar to this one : Solved: Difference between two list of values - Microsoft Power BI Community), but since I need to do this operation several times as I have multiple pair of columns that I need to compare, therefore I leave this approach at the bottom of the list. Is there a way to do this comparison in M Query or DAX or in general in Power BI (maybe R or Python too, do not know how to directly convert a list column to a panadas dataframe column) without expanding and unpivoting, I guess I am mainly looking for some programming or a quick way rather than building multiple tables and then joining them together (i.e expanding and unpivoting)

Sample of my columns:

[Anonymous]

Thanks @rajulshah , I had to apply the Text.Combine function to return a nice string rather than a list

Text.Combine

Above function only works on string(text) , so the correct version is

Text.Combine(List.Difference({"1", "2", "3", "4", "5"}, {"4", "5", "3"}),",")

and the output

Maybe, just plz update your solution to accomodate this so the next person can jump to the solution quickly

[rajulshah · Accepted solution]

Hello @Anonymous ,

Maybe the following M Query function can be helpful to you.
https://docs.microsoft.com/en-us/powerquery-m/list-difference
You can also go through all the List functions and see if any function fits your requirement.

Here, the user has used as following:
Text.Combine(List.Difference({"1", "2", "3", "4", "5"}, {"4", "5", "3"}),","). You can see the solution in his reply.

Let me know if this didn't help.