Turn column with commas into multiple rows

[laurahoff97]

I have a dataset that I need to transform in powerbi, but I am unsure of the right commands to get it how I want. 

Below is an example of how my data currently looks. However I want to take the row IT Owners and make a separate row for each of the names that holds all the values from the other columns

IT Owners	Company Code	Manager	Executive Owner	Tags
Nick, Coach, Winston	12	Schmidt	Jess	Vendor1
Jake, Amy	11	Holt	Kevin	Vendor 1, Vendor 2
Michael, Jim, Pam, Dwight	15	Jan	David	Vendor 2
Andy, Donna, April	21	Leslie	Ron	Vendor 1, Vendor 3

I want it to look like this in the end:

IT Owners	Company Code	Manager	Executive Owner	Tags
Nick	12	Schmidt	Jess	Vendor1
Coach	12	Schmidt	Jess	Vendor1
Winston	12	Schmidt	Jess	Vendor1
Jake	11	Holt	Kevin	Vendor1, Vendor2
Amy	11	Holt	Kevin	Vendor1, Vendor2
Michael	15	Jan	David	Vendor2
Jim	15	Jan	David	Vendor2
Pam	15	Jan	David	Vendor2
Dwight	15	Jan	David	Vendor2
Andy	21	Leslie	Ron	Vendor1, Vendor3
Donna	21	Leslie	Ron	Vendor1, Vendor3
April	21	Leslie	Ron	Vendor1, Vendor3

Any help would be appreciated!

[laurahoff97]

Using split by delimiter and into row seemed to work easier

[MFelix · Accepted solution]

Hi @laurahoff97 ,

For this you just need to use the split rows by delimiter and select the option rows on the the advance:

[Cookistador]

Hi @laurahoff97 

The easiest way to achieve that is to create a new table in Power query(reference of your existing table)
Delete all rows except IT owner and Company Code

Split IT Owner by delimiter: ,

Select Company Code and Unpivot other column

Remove Attribute column

You should get something like that:

Then you close and apply you link your new table to the previous one

Once your two tables are linked (via Company Code), you create  a table, take IT owner from the new table and other fields from the previous one

this is the code I used in Power Query

let
    Source = ITTable,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Manager", "Executive Owner", "Tags"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "IT Owners", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"IT Owners.1", "IT Owners.2", "IT Owners.3", "IT Owners.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"IT Owners.1", type text}, {"IT Owners.2", type text}, {"IT Owners.3", type text}, {"IT Owners.4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Code"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns1"