Create a list with Power Query and apply List.Mode

[C9SA]

Hi all,

For example I have a column "Colours" which holds values and are separated by a comma:

Product	Colours	List	List.Mode
A	Green, Red, Green, Cyan, Blue, Yellow, Orange, Orange, Green, Green	[List]	Green
B	Red, Green, Cyan, Blue, Yellow, Orange, Orange, Green	[List]	Orange

I'm in search of a Power Query syntax which enables me to turn the column "Colours" into a List within the same table, so I can make use of List.Mode in order to return the item that appears most frequently in the list.

Note: I am aware of the built-in Convert to List (Transform ribbon) or Drill Down function, which doesn't turn the column into the desired "List" which can be used with List.Mode, nor do I want to apply DAX measures in order to return the most frequently item in each row.

Any suggestions?

[mahoneypat · Accepted solution]

You can add a custom column in the query editor and use Text.Split([Coulors], ",") to get a column of lists.

Regards,

Pat

[C9SA]

Thanks!