How to Split Columns with Power Query with Variable Delimiters

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

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

Nice!

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

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

Love it and thank you for sharing

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

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.

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

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

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

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

Super trick

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"

Hi Oz,

Thank you, great video...i like your theatrical approach to teach excel! ;-) Below is my take on the problem, of course your solution amended my Marcel"s suggestion has fewer steps but anyway I thought i should share...

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "count_comma", each List.Count(List.PositionOfAny(Text.ToList([TEAMS]),{","},Occurrence.All))),
nb_col = #"Added Custom"[count_comma],
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "TEAMS", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), List.Max(nb_col)+1),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"count_comma"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"TEAMS.1"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"TEAMS.1", "TEAMS"}})
in
#"Renamed Columns"

Hot tip!

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