Getting A Semi Colon Between String Instead of Comma

Good Afternoon!

I hope everyone is having a great day!

I am working on automating my student enrollment CSV data scrubs that I pull from Knack in Power Query editor prior to importing into FLVS. Our students can choose courses from multi select fields so they will only have to fill out a single form. Due to that my export has their courses selected separated by a comma:

2 DIMENSIONAL STUD ART 1, 101300 SEG A,2 DIMENSIONAL STUD ART 1, 101300 SEG B

What I would like to do is first split the arrays (courses) then break out the course codes. To do this I need a different delimiter between the arrays/courses. Is this possible in Knack?

Thanks!

Hi @Dan3, you won’t be able to export to CSV with semi-colons, but in Power Query you should be able to split by the first ‘empty space’ delimiter, assuming all your course codes precede the course name with a space.

Thanks @StephenChapman unfortunately due to some state naming conventions with course names, i have to adjust my delimiters. Instead I simply replaced the comma between the course name and code with a semicolon so now I can split out my multiple courses using the comma delimiter, then separate the course code from the course array with the semicolon delimiter and then the segment (if needed), with a space delimiter. Lots of back end loading in Power Query BUT this should take a 30-40 minute daily process to about 5 so its worth. Thanks for trying! Have a great day!

Dan

1 Like