Forum Discussion

Yuvaraj's avatar
Yuvaraj
Icon for Helper I rankHelper I
9 years ago
Solved

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 grou...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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's avatar
Yuvaraj
Icon for Helper I rankHelper I
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    Yuvaraj
    Icon for Helper I rankHelper I
    9 years ago

    Hi Pfister,

    I was in leave past few days....

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • Yuvaraj's avatar
        Yuvaraj
        Icon for Helper I rankHelper I
        9 years ago

        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