Forum Discussion
Helper IHow to extract the data between special characters
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
Community ChampionCan you split the column twice once by * and the &
Then again there may be a better solution ImkeF or MarcelBeug
YuvarajI tried this method but its remove the some comments after split the text thats why there is any other method to do
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..
- Sean
Do you mean text after the & ?
This image (different from above) takes care of this scenario... :smileyhappy:
- Yuvaraj
yes