Need help splitting rows with no delimiter

[Anonymous]

I am new to powerBI and have what I think is a tough problem to solve.  I have a large dataset with several rows similar to the below.  Is there a way to split the columns so that each individual line in column 3 is split into its own row and has the identifying information from column 1&2?

Column 1	Column 2	Column 3
A	B	C - 1231 D - 12.12 E - 123154.123123

[parry2k]

@Anonymous If you try split in Power Query to columns and then use fill down on transform table for column1 and column 2 , it will do it, but as @HotChilli suggested, how your real data looks like?

[HotChilli]

In the form displayed, line 1 and 3 don't know anything about A and B so the data would have to be manipulated first.

Does your real data look like this ?  If not, can you post a more realistic sample so that any pattern can be found.

[Anonymous]

Yes, the data actually looks like this.  In the real data, 2-25 rows of unique data will be found within a single cell in Column 3.  

One pattern could be that each unique row within column 3 represents a dollar figure. so a delimiter could be found by looking at 2 characters after a "." 

[parry2k]

@Anonymous check steps in the query editor in the attached file and change as per your data model.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Hi All,

Thank you for your help but I was able to find a hidden delimeter that helped split the column into rows.  PowerBI natively allows for split of unicode text via "Carriage Returns".  There are a few of these you can pick, but the one that was used in my data was not referenced. 

This is a character called a "Line Feed".   I was able to split by using the special character #(lf)

[Anonymous · Accepted solution]

Hi @Anonymous ,

So you split Column 3 by delimiter "line feed" to achieve it, right? Thanks for sharing your solution. Could you please mark your post as Answered if the problem has been resolved? Thank you.

Best Regards

Rena