Split data in 1 column while retaining data in other columns

July 20, 2023

I have data that comes in to me daily that I am manually separating column B (Node Area) into individual rows. I am looking for help creating a solution that will retain the rest of data in the row and split out Column B by carriage return.

I have been playing with Power Query as I think that would be the way to go, but when I am trying to split column B by Carriage Return delimiter, nothing happens. Not sure if I missed something, but the m-code is below

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Available Days", type any}, {"Node Area", type text}, {"Tech Name", type text}, {"Cell Number", type text}, {"MA", type text}, {"Title", type text}, {"Work Type", type text}, {"Pick Up Location", type text}, {"Pickup Time", type time}, {"Truck #", type any}, {"MT Skill Sets", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Node Area", Splitter.SplitTextByDelimiter("#(#)(cr)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Node Area"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Node Area", type text}})
in
    #"Changed Type1"

I have also been looking at creating a formula in excel this works to split column B only TRANSPOSE(TEXTSPLIT(B3,CHAR(10))), but not sure what else I need. Any guidance would be greatly appreciated.

Starting Data

Available Days	Node Area	Tech Name	Cell Number	MA	Title	Work Type	Pick Up Location	Pickup Time	MT Skill Sets
07/20/23	HO006 HO033	Paul Johnson	+ 1 (555) 123-4567	WNY	MT	Daytime Work	Frisby Street	11:15 AM	All of the above
7/20/2023	HO008 HO019 HO047 HO048 HO049	Brad Smith	+ 1 (555) 867-5309	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above
7/20/2023	HO017	Chad Dutch	+ 1 (555) 987-6543	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above

After Editing

Available Days	Node Area	Tech Name	Cell Number	MA	Title	Work Type	Pick Up Location	Pickup Time	MT Skill Sets
7/20/2023	HO006	Paul Johnson	+ 1 (555) 123-4567	WNY	MT	Daytime Work	Frisby Street	11:15 AM	All of the above
7/20/2023	HO033	Paul Johnson	+ 1 (555) 123-4567	WNY	MT	Daytime Work	Frisby Street	11:15 PM	All of the above
7/20/2023	HO008	Brad Smith	+ 1 (555) 867-5309	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above
7/20/2023	HO019	Brad Smith	+ 1 (555) 867-5309	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above
7/20/2023	HO047	Brad Smith	+ 1 (555) 867-5309	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above
7/20/2023	HO048	Brad Smith	+ 1 (555) 867-5309	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above
7/20/2023	HO049	Brad Smith	+ 1 (555) 867-5309	WNY	MT	Overnight Work	Frisby Street	11:00 PM	All of the above
7/20/2023	HO017	Chad Dutch	+ 1 (555) 987-6543	ENY	MT	Overnight Work	Frisby Street	11:30 PM	All of the Above