Forum Discussion
Need help splitting column
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!
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'
2 Replies
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-0be331ab387a
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'
