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.
Hello, Mark. Welcome to Power Query and welcome to Excel on Fire!! Hopefully you'll find more of what you need here so that you can get your data to do what you need it to do! 🎸🎸🎸💥 Power Query will make your life so much easier.
@@OzduSoleilDATA Hi Sir, I am new to power query. I need your help in resolving a issue I am facing while splitting data to columns. Can you please help.
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
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.
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"
Yes! One thing I came across, though is Split into Rows isn't available to everyone yet. I could have pointed that out in this video. For those of us who have Split into Rows, that's the way to go. I showed that in a previous video and that's when I got emails from people saying, "I don't see that on my version of Excel. Where is mine?" OOPS! They don't have it yet.
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
@@OzduSoleilDATA but once i split this into column again its splitting into specific no of columns which will not change later if i add something in my source data and refresh the query.
I achieve my desire result using List.Transform Table.SplitColumn with Splitter.SplitTextByDelimiter Thanks for the trick which made me think that it is possible. 😊
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.
Marcel! THANK YOU!!!!! Thank you!
This was giving me fits for about 2 weeks.
Thanks for making it even simpler. 🔥🔥🔥
@@OzduSoleilDATA I used Split.Text as a custom formula, as i have multiple cols to form part of my report. Any Warnings?
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!
Watch your fingers! Be safe out there. 😄
Great video. I am new to Power Query and the video was exactly what I needed. Thanks, Oz.
Hello, Mark. Welcome to Power Query and welcome to Excel on Fire!! Hopefully you'll find more of what you need here so that you can get your data to do what you need it to do! 🎸🎸🎸💥
Power Query will make your life so much easier.
Thanks! , Can't lie I jumped when that explosion went off LOL . .. Good Stuff subscribing now!
Welcome welcome! Thanks for dropping by and subscribing.
I didn't mean to scare you with the explosion.
😊
@@OzduSoleilDATA Hi Sir, I am new to power query. I need your help in resolving a issue I am facing while splitting data to columns. Can you please help.
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 for the suggestions. 😊
I never imagine this at power quert,thanks a lot of.
Fernando Machado Rocha you're so welcome!
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
Hot tip!
GURU JI.. you are awesome.. no words..
Thank you. Thank you! 🔥🔥🔥🔥🔥
PERFECT! Helped a lot!
Love it and thank you for sharing
You're very welcome! 🤙🏾
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.
Yes. This is still very much a problem. Big headache.
@@OzduSoleilDATA wow. that is absolutely ridiculous. They should take note from 'tables' and have the steps be variable... shakes head.
Thanks Oz very Nice tricks and Handy
is there a link to the workbook?
Yes. Here's a link: datascopic.net/trapcrap
Nice!
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"
Yes! One thing I came across, though is Split into Rows isn't available to everyone yet. I could have pointed that out in this video.
For those of us who have Split into Rows, that's the way to go. I showed that in a previous video and that's when I got emails from people saying, "I don't see that on my version of Excel. Where is mine?"
OOPS! They don't have it yet.
Super trick
Thank you!
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
Yes. You can split by delimiter. Or, maybe you're asking something more complex.
@@OzduSoleilDATA but once i split this into column again its splitting into specific no of columns which will not change later if i add something in my source data and refresh the query.
I achieve my desire result using
List.Transform
Table.SplitColumn with Splitter.SplitTextByDelimiter
Thanks for the trick which made me think that it is possible. 😊