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

#### Frazzle

##### New Member
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:

 Website Items Price Quantity Ebay Chair Bed Table 15 100 50 4 1 1 Madeup.com Carpet 100 2 Amazon Lamp Couch 20 100 4 1

<tbody>
</tbody>

What I would like:

 Website Items Price Quantity Ebay Chair 15 4 Ebay Bed 100 1 Ebay Table 50 1 Madeup.com Carpet 100 2 Amazon Lamp 20 4 Amazon Couch 100 1

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### horseyride

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

#### sandy666

##### Well-known Member
1. duplicate table twice
2. from the first remove Price and Quantity
3. split Items by lf
5. from the second remove Items and Quantity
6. split Price by lf
8. from the third remove Items and Price
9. split Quantity by lf
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

 Website Items Price Quantity Ebay Chair Bed Table 15 100 50 4 1 1 Madeup.com Carpet 100​ 2​ Amazon Lamp Couch 20 100 4 1 Website Items Price Quantity Ebay Chair 15 4 Ebay Bed 100 1 Ebay Table 50 1 Madeup.com Carpet 100 2 Amazon Lamp 20 4 Amazon Couch 100 1

Last edited:

#### horseyride

##### Board Regular
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

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Website", type text}, {"Items", type text}, {"Price", type text}, {"Quantity", type text}}),
#"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

##### Active Member
Other way
Code:
``````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"``````

Last edited:

#### Frazzle

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

You are welcome