imported table of data has some rows with line breaks that need to be split into 2 rows

[Pandadev]

I am trying to split the rows where there is additional info after line breaks , so for example flight 106 would have 2 rows showing , is this possible in PowerBI please

flight number	Route	Departure days	departure time	time  arrival
106	Osh-Bishkek	Mon, Tue, Thu, Sat, Sun	09:30	10:10
106	Osh-Bishkek	, Wed, Fri	13:15	13:55

Original table below

link is  http://tezjet.kg/schedule.html

Schedule.
flight number	Route	Departure days	departure time	time  arrival
106	Osh-Bishkek	Mon, Tue, Thu, Sat, Sun , Wed, Fri	09:30 13:15	10:10 13:55
120	Osh-Bishkek	Mon, Wed, Fri Tue, Thu, Sun	1 7 : 2 5 20:40	1 8 : 0 5 21:20
130	Osh-Bishkek	daily	18:00 _ _	1 8 : 4 0
132	Osh-Bishkek	daily except Fri	08:35 _ _	09:20 _ _
118	Osh-Bishkek	on Tue, Thu, Sat, Sun	2 1:40 _	2 2:20 _
128	Osh-Bishkek	on Wed, Fri	14:10	14:50
10 8	Batken-Bishkek	on Tue	13:15	14:15
11 6	Jalal-Abad-Bishkek	Mon, Tue, Thu, Sat, Sun  on Fri	13:00 11:00	13:40 11:40
110	Isfana-Bishkek	on Wed and Sat	13:05	14:20
126	Tamchy - Osh	on Wed and Fri  on Tue, Thu,  sun	11:30 19:00	12:15 19:45

[amitchandak]

@Pandadev , If you want to have two lines try to add unichar(10) to the string

"ABC" & unichar(10) & "DEF"

[Pandadev]

Thanks , I cant seem to work out how to apply your logic , here is my script so far

let
Source = Web.BrowserContents("http://tezjet.kg/schedule.html"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.MsoNormalTable:nth-child(1) > * > TR > TD[colspan=""5""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE.MsoNormalTable:nth-child(1) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5)"}, {"Column2", "TABLE.MsoNormalTable:nth-child(1) > * > TR > TD[colspan=""5""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE.MsoNormalTable:nth-child(1) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4)"}, {"Column3", "TABLE.MsoNormalTable:nth-child(1) > * > TR > TD[colspan=""5""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE.MsoNormalTable:nth-child(1) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3)"}, {"Column4", "TABLE.MsoNormalTable:nth-child(1) > * > TR > TD[colspan=""5""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE.MsoNormalTable:nth-child(1) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2)"}, {"Column5", "TABLE.MsoNormalTable:nth-child(1) > * > TR > TD[colspan=""5""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE.MsoNormalTable:nth-child(1) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(1)"}}, [RowSelector="TABLE.MsoNormalTable:nth-child(1) > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"№ рейса", "Flight Number"}, {"Маршрут", "Route"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","ежедневно","1234567",Replacer.ReplaceText,{"Дни вылета"}),
#"Renamed Columns2" = Table.RenameColumns(#"Replaced Value",{{"Дни вылета", "Departure Days"}, {"время вылета", "Dep Time"}, {"время #(lf) прибытия", "Arr Time"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns2","по 2, 4, 6, 7 дням","2467",Replacer.ReplaceText,{"Departure Days"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","по 3, 5  дням","35",Replacer.ReplaceText,{"Departure Days"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","по 2, 4 дням","24",Replacer.ReplaceText,{"Departure Days"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","по 1, 5 ,7 дням","157",Replacer.ReplaceText,{"Departure Days"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","по 3, 6 дням","36",Replacer.ReplaceText,{"Departure Days"})
in
#"Replaced Value5"

[Anonymous · Accepted solution]

Hi @Pandadev ,

You can add the step "Split Column by Delimiter" in Power Query Editor:

1. Select the column "Дни вылета" and click Split Column--> By Delimiter

2. Split by line breaks as below screenshot

= Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns1", {{"Дни вылета", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Дни вылета")

In addition, you can refer the following blog to get it.

Split Column by Carriage Return in Power Query

Best Regards