Forum Discussion

C9SA's avatar
C9SA
Frequent Visitor
5 years ago
Solved

Create a list with Power Query and apply List.Mode

Hi all,

 

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

 

ProductColoursListList.Mode
AGreen, Red, Green, Cyan, Blue, Yellow, Orange, Orange, Green, Green[List]Green
BRed, 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?

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

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

    Regards,

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

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

    Regards,

    Pat