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 a text like : "AA-BBB-CCCC-1234567:1-1-7-3-"
I get following M formula , which is working , hovewer
is there any loop , or list feature to get it in less code ?
I expect that XXn is in range of n {1..99} , the same for YYY is in range of n {1..99}

=Text.Combine(
List.ReplaceMatchingItems(
Text.Split([hpd_ci], "."),
{
{"R1",":1-"},
{"S1","1-"},
{"XX1","1-"},
{"XX2","2-"},
......
{"XX99","99-"},
{"YYY1}", "{1-}"},
{"YYY2}", "{2-}"},
......
{"YYY99}", "{99-}"},
}
)
)

 

Can anyone has some idea how to not type 99 times the same formula ?

 

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

2 Replies

  • 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

     

  • Aleks13's avatar
    Aleks13
    Frequent Visitor
    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