Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bcolgan4
Regular Visitor

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

 

 

bcolgan4_0-1670541258265.png

bcolgan4_1-1670541328933.png

 

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!

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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'

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

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_
Super User
Super User

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...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.