Need help splitting column

[bcolgan4]

Hey!

I am having trouble breaking this CSV file out into distinct columns. The file is a csv directory consisting of lines indicating a ProductID, followed by a colon, and then customer ids and rating dates, one per line for that ProductID. There are no empty lines in the file. For example:

Column1, Column2:

ProductID1:

CustomerID11, Date11
CustomerID12,Date12
...
ProductID2:
CustomerID21,Date21
CustomerID22,Date22

How do I break out a single column for each item? I've tried to used Split Column and delimiters but can't seem to get that to work. 

Column1, Column2, Column3

Product ID1, CustomerID11, Date11

ProductID1, CustomerID12, Date 12

ProductID1, CustomerIDI3, Date13

......

Product ID2, CustomerID21, Date21

ProductID2, CustomerID22, Date 22

.......

ProductID3, CustomerID31, Date31

I appreciate the help!

[HotChilli · Accepted solution]

In power query, add a column using 'if then else'

The logic is : if column2 is empty or null or whatever then column1 else null

Then do a 'fill Down' on the new column.

Then filter out the null/empty rows from Column2

---

Alternatively, search the text in Column1 to see if contains ':' to make a slightly different 'if  then else'

[vicky_]

You will need to go into Power Query and go to Transform > Pivot Column on the Items column. See this page for a better explanation of how pivoting works: https://support.microsoft.com/en-us/office/pivot-columns-power-query-abc9c8da-3be9-44c4-886e-0be331a...