How to Extract the Text between the delimiters in Power BI Query

Question

I want to add a custom column that extracts the text between the delimiters "~"

This is the input :

This is the output which I am expecting:

I have tried below query, but its not working

=Text.Select([#"Comments"]
{"A".."Z"} & {"1".."10"}&{"~"})

Could you please suggest me

Answer 1

This works in powerquery (M)

It assumes data is coming from Table1, into Column 1 Adjust code if that is not true

It also assumes there is a space between the ending ~ and the next word

 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Replaced Value" = Table.ReplaceValue(Source,"~ ","::",Replacer.ReplaceText,{"Column1"}),
 #"Adjust edge case" = Table.TransformColumns(#"Replaced Value",{{"Column1", each if Text.End(_,1)="~" then Text.Start(_,Text.Length(_)-1) &"::" else _, type text}}),
 #"Added Custom" = Table.AddColumn(#"Adjust edge case", "Custom", each List.Difference(List.Transform(Text.Split([Column1],"~"), each Text.BeforeDelimiter(_,"::")),{""})),
 ColumnNames=List.Transform({1..List.Max(List.Transform(#"Added Custom"[Custom], each List.Count(_)))}, each "Data "&Text.From(_)),
 #"Added Custom2" = Table.AddColumn(#"Adjust edge case", "Custom", each Text.Combine(List.Skip(List.Transform(Text.Split([Column1],"~"), each Text.BeforeDelimiter(_,"::")),1),"~")),
 #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom2", "Custom", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), ColumnNames)
 in  #"Split Column by Delimiter"