Kudos for ths video! 1 Remark: around 3:27, you can change "type text" to "type list" in the step where you split the cell values. Then you don't need to convert to list and back to table again.

This level of automation is amazing, thank you so much for making these kind of videos.

Caught my fingers and this is exactly what I needed. Thank you!

Great video. I am new to Power Query and the video was exactly what I needed. Thanks, Oz.

Thanks a lot for this one, and for linking to other solutions. I used to just delete the generated columns list as Imke suggested at first, and was not aware of the fact that this way it took the first row as a template! (*broken fingers sound*). Now I know several approaches to tackle this issue (which is still there and quite unbelievably so) I'll add a slight variation which does not require for the word "Team" to be included in the team name, by duplicating the column, extracting all before first delimiter in the first one and all after first delimiter in the second one: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Duplicate_column = Table.DuplicateColumn(Source, "TEAMS", "Members"), Team_name = Table.TransformColumns(Duplicate_column, {{"TEAMS", each Text.BeforeDelimiter(_, ", "), type text}}), Member_list = Table.TransformColumns(Team_name, {{"Members", each Text.AfterDelimiter(_, ", "), type text}}), Split = Table.TransformColumns(Member_list,{{"Members", Splitter.SplitTextByDelimiter(", ")}}), Expanded = Table.ExpandListColumn(Split, "Members") in Expanded

Hello, Oz. Super movie ! I've learned a lot of cool things from you. You inspire me. I thought: what if the name of the team will be, for example, "Dinosaurs" or "Rats"? I came up with such a simple solution 1. On Column TEAM : Split by delimiter >>> option: “Custom” & “, “ and Split at Left-most delimiter 2. On (new) Column TEAM.2 : Split by delimiter >>>>option : “Custom” & “, “ Advanced option Rows 3. Rename columns TEAM.1 and TEAM.2 READY ! Greetings from Poland

I never imagine this at power quert,thanks a lot of.

PERFECT! Helped a lot!

Thanks! , Can't lie I jumped when that explosion went off LOL . .. Good Stuff subscribing now!

Hot tip!

GURU JI.. you are awesome.. no words..

Love it and thank you for sharing

Nice!

Thanks Oz very Nice tricks and Handy is there a link to the workbook?

Super trick

Is this still a thing to account for within Power Query? This doesn't seem very user friendly and is not expected behavior. Probably a lot of people will be running into issues with Power Query because of things like this. Good video, just too bad Power Query doesn't operate in the expected manner.

Thats an awesome trick. Thank you for sharing. I have 3 rows available like this and i want this to split into columns not in rows. Is that possible? ROW1: Kevin,Kevin,Kevin,Kevin,Pete,Pete,Pete,Nathan,Nathan,Luke ROW2: Jon,Jon,Jon,Edward,Kyle,Kyle,Max ROW3: Joe,Austin,Austin,Sam,Sam,Sam

You could also split by columns to initially get the Team Number then split by Rows to get the list of players. This also works with updates because the "split by rows" does not store the row count like "split by columns"