How to deal with list columns in PowerBI

[Thundercoder]

Hi guys, 

My question is two fold- 

I have a dataset that consists of a list of employees and the revenue generated per shift.  Each shift a group of employees works and the store records the revenue.  

Say I have a text column in my dataset that consists of a list of names of the employees (Each row in the column would be a list such as cell a2 = Amy, Derek Wendy and cell a3 = Fiona, Wendy).   Does PowerBI have a list data type that can make sense of such a column?  How can I convert that column into a list data type and delimit it by ",".  

Also, once I do that how can I display a table grouped by the names of the people that were in the column and show their total revenue contribution or average revenue contribution per shift.   (for example if Wendy's name appeared in 3 rows, sum all of the revenue generated by Wendy's rows, or get the average revenue per shift she worked).

thanks! Let me know if I need to provide anymore details and if such is possible in powerbi 

[danextian]

Hi @Thundercoder ,

There are a lot of data transformations you can do with Power BI. Some may not be drag and drop but are available using the M language. Text.Split might apply for your use-case but it can't be sure without seeing the data. Can you please post a sample data which we can easily manipulate (not an image)?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150

[Thundercoder]

Thanks so much for taking the time!  I posted a short example table below since it isnt letting me attach a file.   What I want to do is use powerbi to return a table of each employee and how much revenue each generated.     Perhaps I need to create a new query and build a new table based on the employees column and then create a relationship?   I just dont know how to go about doing this, thanks!

Date	Employees	Shift	Revenue
2022-08-25 10:30	Fiona, Wendy	1	400
2022-08-25 15:54	Jim, Wendy, Tim	2	330
2022-08-26 10:52	Fiona, Jim	1	360
2022-08-26 15:55	Fiona, Tim, Colin	2	420
2022-08-27 10:40	Wendy, Colin	1	385

[danextian · Accepted solution]

Hi @Thundercoder ,

I am not sure why you want to convert the employee column into a list but you can use Text.Split to do that. I've attached a pbix with two options: Text.Split and splitting a column into several columns based on a delimeter.  You can see the steps when you go to Query Editor.

[Ashish_Mathur]

Hi,

In the Query Editor, you can split the Employees column into rows.

Hope this helps.