Forum Discussion
informer
1 year agoHelper I
Power Query -Delete column substring corresponding to the text of another column (insensitive case)
Hey I have the data below in table Table1 whose data was loaded from a web page Nom Acronyme Volume Circulating Supply RONRonin RON $51,814,419ron 46,960,396 619,389,699 RON Stark...
- 1 year ago
You merely need to modify the Table.ReplaceValue function I provided you in your previous similar question:
#"Remove Acronyme" = Table.ReplaceValue( #"Previous Step", each [Acronyme], null, (x,y,z) as text => let pos = Text.PositionOf(x,y,Occurrence.All,Comparer.OrdinalIgnoreCase) in List.Accumulate( List.Reverse(pos), x, (s,c)=> Text.RemoveRange(s,c,Text.Length(y)) ), {"Nom","Volume","Circulating Supply"})#"Previous Step"
#"Remove Acronyme"
Note that in the first row of the first column, it is removing both instances of the Acronyme. Depending on exactly what you want to do in that instance, you may need to change the logic a bit.
BeaBF
1 year agoSuper User
informer Hi! Try with:
= Table.TransformColumns(
#"Order col",
{"Nom", "Volume", "Circulating Supply"},
each (textValue) =>
Text.Trim(
Text.Replace(
Text.Replace(
Text.Replace(textValue, Text.Lower(_[Acronyme]), "", Comparer.OrdinalIgnoreCase),
Text.Upper(_[Acronyme]), "", Comparer.OrdinalIgnoreCase
),
_[Acronyme], "", Comparer.OrdinalIgnoreCase
)
),
type text
)
BBF