How to extract the data between special characters

[Yuvaraj]

Hi,

I am new to PowerBI, In below table i need to extract ID between the special characters(* and &).Could you please help me 

Comments

1.Found the root cause of "Reachability bill group issue"& documented it. 2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue increamendal load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on GAS ANL enhancement Poc *ALM# 2720&

Support moitoring process AMI Deployment activity*IM001375780& Order life cycle loading issue

Regards,

Yuvaraj 

[Sean]

Can you split the column twice once by * and the &

Then again there may be a better solution @ImkeF or @MarcelBeug

[Yuvaraj]

I tried this method but its remove the some comments after split the text thats why there is any other method to do

[Sean]

Do you mean text after the & ?

This image (different from above) takes care of this scenario...

[MarcelBeug]

@Sean

If I understand correctly, the original content should be kept. If so, the column should first be duplicated and then the copy can be split.

I would probably come up with something similar as the solution provided by @Anonymous (but with null intsead of "" in case of no hit).

Otherwise I would suggest @Yuvaraj to provide the expected result, as the requirements are hard to understand.

[Yuvaraj]

yes

[Anonymous]

Hi Yuvaraj

Please try the code below.

Hope it will solve your problem.

Greetings

Hp Pfister

=Table.AddColumn(<YOURLASTSTEP>, "column1", each 
if Text.PositionOf([Comments],"*") +1 = 0 then "" else
Text.Range([Comments],
Text.PositionOf([Comments],"*")+1,
Text.PositionOf([Comments],"&")-1-Text.PositionOf([Comments],"*")))

[Yuvaraj]

Hi Pfister,

I was in leave past few days....

could you please give brief explanation on it. i am getting error..

[Anonymous]

Hi Yuvaraj
What kind of error did you get?
Can you please post a picture of it?

[Yuvaraj]

I just click on add column and paste your expression showing this error.

Error:

The syntax for 'Table' is incorrect. (DAX(Table.AddColumn(<YOURLASTSTEP>, "column1", each if Text.PositionOf([Comments],"*") +1 = 0 then "" elseText.Range([Comments],Text.PositionOf([Comments],"*")+1,Text.PositionOf([Comments],"&")-1-Text.PositionOf([Comments],"*"))))).

Please see the below image 

Regards,

Yuvaraj

[Anonymous]

All right.

You have to enter the code provided in the query editor as it is a "M" Code, not a DAX Code.

[Yuvaraj]

I am trying but didn't get that, could you please attach the GIF file how to do that.

[MarcelBeug]

This video explains how to implement the solution proposed by @Anonymous.

[Yuvaraj]

thanks Marcel this is what am also expecting...

From this i have one doubt in comments are entered single row if the ID are two means it will fetch the first ID row only but i need two ID's.

For Example:

1.Done the CFO data issue increamendal load script wise issue*IM001350816&2.Done the aging no read manufacture code mismatch issue.3.working on GAS ANL enhancement Poc *ALM# 2720&

the above three comments are entered in single row @Anonymous provided code is extract the first ID(IM001350816) but I need second ID also(ALM# 2720).

Expecting Output:

ID	Comments
IM001350816	1.Done the CFO data issue increamendal load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on GAS ANL enhancement Poc*ALM# 2720&
ALM# 2720	1.Done the CFO data issue increamendal load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on GAS ANL enhancement Poc*ALM# 2720&

Regards

Yuvaraj

[MarcelBeug · Accepted solution]

Ah, so far it wasn't clear to me that you had multiple lines in each text field... at least that's what I understand now.

I created an Excel file with some test data (yours and some slubby examples) and the code below in Power BI - Query Editor and another video to illustrate how it works.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\How to extract text between special characters.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Comments", type text}}),
    AddedStart = Table.AddColumn(#"Changed Type", "Start", each Text.PositionOf([Comments],"*",Occurrence.All)),
    ExpandedStart = Table.ExpandListColumn(AddedStart, "Start"),
    StartPlus1 = Table.TransformColumns(ExpandedStart, {{"Start", each _ + 1, type number}}),
    AddedLength = Table.AddColumn(StartPlus1, "Length", each if [Start] = null then null else Text.PositionOf(Text.Range([Comments],[Start]),"&",Occurrence.First)),
    AddedID = Table.AddColumn(AddedLength, "ID", each if [Start] = null or [Length] = -1 then null else Text.Range([Comments],[Start],[Length])),
    RemovedOtherColumns = Table.SelectColumns(AddedID,{"ID", "Comments"}),
    RemovedDuplicates = Table.Distinct(RemovedOtherColumns,{"ID","Comments"})
in
    RemovedDuplicates

[Yuvaraj]

Thanks for you valuble time Marcel. I am also expecting this