Separate informations from column B into many columns based on column A

[MarcosGrod]

I'm trying to separate all the data from a single columns that contains many different informations based on a key from another column. Some of them have key words like "CEP:", "Tel:" or "Cel:", but there are some that don't, and some cases where there are more than 1 of those keywords with different values after them for each key from column 1 ("Igreja"). Is there a way to do it?

(i'm learning how to use Power BI)

[Anonymous]

Hi @MarcosGrod 

My idea is that for values starting with definite keywords, we can split by ":" first, then use group by or pivot columns later. The difficulty is how to deal with the values without keywords. It looks like they probably fall into more than one category, right? (I'm guessing some might be addresses, but some I'm not quite sure.) 

Is there any way or rule to distinguish which categories these values without keywords should belong to? We need to distinguish and classify before we can make the subsequent transformation.

Best Regards,
Jing

[parry2k]

@MarcosGrod sorry it is not clear what you are trying to achieve, maybe easier to create a sample excel file with the data and the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490