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

Frazzle

New Member
Joined
Nov 6, 2019
Messages
2
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:

WebsiteItemsPriceQuantity
EbayChair
Bed
Table
15
100
50
4
1
1
Madeup.comCarpet1002
AmazonLamp
Couch
20
100
4
1

<tbody>
</tbody>

What I would like:

WebsiteItemsPriceQuantity
EbayChair154
EbayBed1001
EbayTable501
Madeup.comCarpet1002
AmazonLamp204
AmazonCouch1001

<tbody>
</tbody>

Appreciate any help you could provide!
 

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
81
Is Chair, Bed, Table all on different rows? If so, just right click the Website column and Fill dowm
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,311
  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

WebsiteItemsPriceQuantity
EbayChair
Bed
Table
15
100
50
4
1
1
Madeup.comCarpet
100​
2​
AmazonLamp
Couch
20
100
4
1
WebsiteItemsPriceQuantity
EbayChair154
EbayBed1001
EbayTable501
Madeup.comCarpet1002
AmazonLamp204
AmazonCouch1001
 
Last edited:

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
81
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}}),
    #"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

Active Member
Joined
Feb 26, 2014
Messages
344
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
Joined
Nov 6, 2019
Messages
2
Thanks all, interesting to see the different approaches! Much appreciated.
 

Forum statistics

Threads
1,078,444
Messages
5,340,319
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top