Hi, I'm working with tables like this one, with a series of values in the same row: How can I get Column2 split into a row for each value on a new line? Ideally, I would want a table like this: YearPrize1955David 1956Abhi Bhattachararya1957Motilal1958Raj Mehra1959Johnny Walker...... I tried splitting the column by delimiter with a special character and the following options: #(lf)#(cr)<BR/>But none works... This is what I get: Thanks in advance for the help!

Hi Anonymous ； According to you description, You could split column and transpose table, then use List.Combine（） .The steps are as follows： Step1: Split Column Step2: Transpose table Step3: Write a function = Table.ToColumns(#"Removed Columns1") = List.Combine(Custom1) = Table.FromList(Custom2) Step4: Split Column Step5: Replace Values The final output is shown below: In addition, M language is as follows: let Source = Web.BrowserContents("https://en.wikipedia.org/wiki/Amrish_Puri"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.navbox:nth-child(1) > TABLE.nowraplinks.mw-collapsible.autocollapse.navbox-inner > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(1), DIV.navbox:nth-child(1) > TABLE.nowraplinks.mw-collapsible.autocollapse.navbox-inner > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2)"}, {"Column2", "DIV.navbox:nth-child(1) > TABLE.nowraplinks.mw-collapsible.autocollapse.navbox-inner > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(1), DIV.navbox:nth-child(1) > TABLE.nowraplinks.mw-collapsible.autocollapse.navbox-inner > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(1)"}}, [RowSelector="DIV.navbox:nth-child(1) > TABLE.nowraplinks.mw-collapsible.autocollapse.navbox-inner > * > TR"]), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3", "Column2.4", "Column2.5", "Column2.6", "Column2.7", "Column2.8", "Column2.9", "Column2.10", "Column2.11", "Column2.12", "Column2.13", "Column2.14", "Column2.15", "Column2.16", "Column2.17", "Column2.18", "Column2.19", "Column2.20", "Column2.21", "Column2.22", "Column2.23", "Column2.24", "Column2.25"}), #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"), #"Removed Columns1" = Table.RemoveColumns(#"Transposed Table",{"Column1"}), Custom1 = Table.ToColumns(#"Removed Columns1"), Custom2 = List.Combine(Custom1), Custom3 = Table.FromList(Custom2), #"Removed Blank Rows" = Table.SelectRows(Custom3, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Blank Rows", "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter1",")","",Replacer.ReplaceText,{"Column1.2"}), #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Column1.2", "Column1.1"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.2", "Year"}, {"Column1.1", "Prize"}}) in #"Renamed Columns" Best Regards,Community Support Team_ Yalan WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How do I split each row into multiple rows? | Microsoft Fabric Community

Forum Discussion

Related content

Split values in one row into multiple

Splitting columns into multiple rows

Combine text split across multiple rows

Split multiple columns into one row

split values into multiple rows