How do I split each row into multiple rows?

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 Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.