Power Query - split out lines of data in a single cell

[Frazzle]

Hi, I have the following data in excel and am looking for a way to split it out into seperate lines - I was thinking Power Query could be the answer but not sure on the steps I could take. It is a one off data clean so I am also up for doing it in Excel via formula but again not certain on the steps to take. It's about 5000 lines of data.

Current Data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Website[/TD]
[TD]Items[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Ebay[/TD]
[TD]Chair
Bed
Table[/TD]
[TD]15
100
50[/TD]
[TD]4
1
1[/TD]
[/TR]
[TR]
[TD]Madeup.com[/TD]
[TD]Carpet[/TD]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Amazon[/TD]
[TD]Lamp
Couch[/TD]
[TD]20
100[/TD]
[TD]4
1[/TD]
[/TR]
</tbody>[/TABLE]

What I would like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Website[/TD]
[TD]Items[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Ebay[/TD]
[TD]Chair[/TD]
[TD]15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Ebay[/TD]
[TD]Bed[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Ebay[/TD]
[TD]Table[/TD]
[TD]50[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Madeup.com[/TD]
[TD]Carpet[/TD]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Amazon[/TD]
[TD]Lamp[/TD]
[TD]20[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Amazon[/TD]
[TD]Couch[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Appreciate any help you could provide!

 

[horseyride]

Is Chair, Bed, Table all on different rows? If so, just right click the Website column and Fill dowm

 

[sandy666]

1. duplicate table twice
2. from the first remove Price and Quantity
3. split Items by lf
4. add Index
5. from the second remove Items and Quantity
6. split Price by lf
7. add index
8. from the third remove Items and Price
9. split Quantity by lf
10. add Index
11. merge 1st table with table2 by Index
12. expand
13. merge merged table with table3 by index
14. expand
15. remove Index

I hope I didn't miss something

it can be done in single optimised Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Website[/td][td=bgcolor:#5B9BD5]Items[/td][td=bgcolor:#5B9BD5]Price[/td][td=bgcolor:#5B9BD5]Quantity[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Ebay[/td][td=bgcolor:#DDEBF7]Chair
Bed
Table[/td][td=bgcolor:#DDEBF7]15
100
50[/td][td=bgcolor:#DDEBF7]4
1
1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Madeup.com[/td][td]Carpet[/td][td]

100​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Amazon[/td][td=bgcolor:#DDEBF7]Lamp
Couch[/td][td=bgcolor:#DDEBF7]20
100[/td][td=bgcolor:#DDEBF7]4
1[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Website[/td][td=bgcolor:#70AD47]Items[/td][td=bgcolor:#70AD47]Price[/td][td=bgcolor:#70AD47]Quantity[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ebay[/td][td=bgcolor:#E2EFDA]Chair[/td][td=bgcolor:#E2EFDA]15[/td][td=bgcolor:#E2EFDA]4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ebay[/td][td]Bed[/td][td]100[/td][td]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ebay[/td][td=bgcolor:#E2EFDA]Table[/td][td=bgcolor:#E2EFDA]50[/td][td=bgcolor:#E2EFDA]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Madeup.com[/td][td]Carpet[/td][td]100[/td][td]2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Amazon[/td][td=bgcolor:#E2EFDA]Lamp[/td][td=bgcolor:#E2EFDA]20[/td][td=bgcolor:#E2EFDA]4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Amazon[/td][td]Couch[/td][td]100[/td][td]1[/td][/tr]
[/table]

 

[horseyride]

Ah. If everything is in same cell, you can do this, no merging or indexing necessary

* Add custom columns that use Text.Split to create split lists for each column
* Create a table from those columns
* Expand
* Fill down

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Website", type text}, {"Items", type text}, {"Price", type text}, {"Quantity", type text}}),
    #"Added Custom0" = Table.AddColumn(#"Changed Type",  "Custom1", each Text.Split([Website],"#(lf)")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom0", "Custom2", each Text.Split([Items],"#(lf)")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom3", each Text.Split([Price],"#(lf)")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom4", each Text.Split([Quantity],"#(lf)")),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom5", each Table.FromColumns({[Custom1],[Custom2],[Custom3],[Custom4]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom4",{"Custom5"}),
    #"Expanded Custom5" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom5", {"Column1", "Column2", "Column3", "Column4"}, {"Website", "Items", "Price", "Quantity"}),
    #"Filled Down" = Table.FillDown(#"Expanded Custom5",{"Website"})
in #"Filled Down"

 

[billszysz]

Other way

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Website", type text}, {"Items", type text}, {"Price", type text}, {"Quantity", type text}}),
    TableTransform = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"), (x) => List.Transform(Record.ToList(x), each Text.Split(_,"#(lf)"))), each Table.FromColumns(_, Table.ColumnNames(Source)))),
    #"Filled Down" = Table.FillDown(TableTransform,{"Website"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Website", type text}, {"Items", type text}, {"Price", type number}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

 

[Frazzle]

Thanks all, interesting to see the different approaches! Much appreciated.

 

[sandy666]

You are welcome