Forum Discussion
How to extract the data between special characters
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
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
15 Replies
Can you split the column twice once by * and the &
Then again there may be a better solution ImkeF or MarcelBeug
I 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],"*")))
