Search code examples
powerbipowerquerypowerbi-desktop

Select string which contains digit at second position and starts with 'R' in power BI


I need to select strings from a list which starts with 'R' and the string should have a digit at second position.

example- R43.1_MAY2012 should be selected but RB_MAY2013 shouldn't.

I tried below code and added the starts with 'R' condition but couldn't figure out how to add the second position digit condition

let
    Source = Excel.Workbook(File.Contents("C:\Users\aditya\Documents\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Release" = Table.AddColumn(#"Changed Type", "Release", each let a = List.Transform(Text.Split([Column1], ";"), each Text.Trim(_)),
    b = List.Select(a, each Text.StartsWith(_,"R"))
    in b{0}?)
    in
    #"Added Release"

Solution

  • A number of options to do this:

     // check if second character is a number
     b = List.Select(a, each Text.StartsWith(_,"R") and Value.Is(Value.FromText(Text.At(_, 1)), type number) )
    
     // check if second character is between "0" to "9"
      b = List.Select(a, each Text.StartsWith(_,"R") and List.Contains({"0".."9"}, Text.At(_, 1) )
    
     // check if first two characters is between "R0" to "R9"
     b = List.Select(a, each List.Contains(List.Transform({"0".."9"}, each "R" & _), Text.Start(_, 2)) )