How do I split each row into multiple rows?

[Anonymous]

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: 

Year	Prize
1955	David
1956	Abhi Bhattachararya
1957	Motilal
1958	Raj Mehra
1959	Johnny 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! 

[v-yalanwu-msft · Accepted solution]

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.

[Anonymous]

Thank you very much! 🙂

[Greg_Deckler]

@Anonymous What is the source? Also, can you share the source file?

[Anonymous]

@Greg_Deckler 

The source is a Wikipedia page: 

en.wikipedia.org/wiki/Amrish_Puri

For now, I split by delimiter with ) and it more or less worked: I still see the breakline before the name I haven't been able to remove it in any way. I have the table I wanted, but it would be nice to know what to use in these cases...

Thanks! 

[Greg_Deckler]

@Anonymous You need to use the Split using special character option. Or you can just edit the M code in Advanced Editor:

Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)

[Anonymous]

Hi @Greg_Deckler 

None of these work for me... 

This is the result: 

😐 

[Greg_Deckler]

@Anonymous Not sure what is different, mine worked first time. See the attached PBIX file below signature.

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "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"}),