Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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!
Solved! Go to 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'
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'
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...
User | Count |
---|---|
89 | |
88 | |
85 | |
81 | |
49 |
User | Count |
---|---|
151 | |
143 | |
111 | |
74 | |
55 |