April 15, 2021
I have been struggling over the last couple of days to figure out how I can split a column that contains multiple rows of information (exports from our system this way).
I have tried splitting by custom delimiter, letting power query select the delimiter, but run into ussies (explained below). I have tried advanced options and using splits by special characters (carriage return, line feed, etc.), and nothing seems to provide the results I need.
My data only contains about 10-12 columns, but I have limited the attached file to the two most important columns ("Sourcing ID" and "Supplier / POC / Number of Responses).
Most of the data could easily be split into columns to show Sourcing ID, Supplier, POC, and Number of responses (most rows do not have multiple data in a cell), which I can split by "/" delimiter.
However, I do have some rows that contain multiple lines of data within a singular cell in the 2nd column. The issue is with the supplier / poc / number or responses column, which I need to split into multiple rows keeping each item within the "Sourcing IDSupplier / POC / Number of Responses" column.
As you can see in this picture and the attached file, many rows are singles and can easily be split by the "/". However, I do have rows that contain 2, 3, 4, up to 15 rows of data in a single cell. At face value, it seems like separating based on a comma "," would be the logical choice; however, I have company names that include a comma (ex: Platcore, LLC), so it spits that interfering with our supplier dim name. The second image is the ideal outcome, but I'm just not sure how to get there, as even if I do split, I'll have up to maybe 12-15 new columns that I then need to associate to a single sourcing iD.
Any help is appreciated
January 31, 2022
The first split you need to do is by Custom delimiter "#(lf)" and press Advance to "Split into Rows" rather than Columns.