Forum Discussion

Aleks13's avatar
Aleks13
Frequent Visitor
4 years ago
Solved

list replace for value indexed - keep index only

I'm translating a table for Power BI with following text in in a each raw , simple to get rid of letters , keep index only see example of text : "AA-BBB-CCCC-1234567:R1.S1.XX7.YYY3" I need to have ...
Tips and Tricks
  • Aleks13's avatar
    Aleks13
    4 years ago

    Hello Rocco

    I did try your solution idea, I forget to add that sometimes my string will be empty after colon , or not complete ( "AA-BBB-CCCC-1234567:R1.S1.XX7.YYY3" , or "AA-BBB-CCCC-1234567", or "AA-BBB-CCCC-1234567:R1.S1) , but with a small edit I get result.

    I think the main idea was to use Text.SplitAny(Text.Replace(after_col,".","-"),Text.Combine({"A".."Z"}))

     

    many thanks

Anonymous's avatar
Anonymous
Not applicable
4 years ago

try to adapt the following script to your use case

 

let
    str="AA-BBB-CCCC-1234567:R1.S1.XX7.YYY3",
    before_col=List.First(Text.Split(str,":")),
    after_col=List.Last(Text.Split(str,":")),
    ac = Text.Combine(Text.SplitAny(Text.Replace(after_col,".","-"),Text.Combine({"A".."Z"})))
in
    before_col&":"&ac