Forum Discussion

juarta's avatar
juarta
Frequent Visitor
6 years ago
Solved

Cambiar tipo de dato a ABC123

Deseo hacer una combinacion pero la columna tiene texto y números, debo de limpiar, recortar colocar en mayuscula y posteriormente hacer la combinacion de datos, la consulta dentro del power query no genera error pero al cargar indica que no puede hacer la cambio del tipo de formato.

 

Hay aforma de pasar el formato número o texto al ABC123 que revise numeros y letras, por que al hacer el limpiar me lo cambia a formato text

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, juarta 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may add a new step with the followind codes to get the data whose format is like 'ABC123'.

    = Table.SelectRows(#"Changed Type",each
let 
data = Text.Trim([Data]),
len = Text.Length(data),
flag1 = List.MatchesAll( Text.ToList( Text.Start(data,3) ),each List.Contains({"a".."z","A".."Z"},_)),
flag2 = List.MatchesAll( Text.ToList( Text.End(data,3)  ),each List.Contains({"0".."9"},_))
in
len=6 and flag1 and flag2
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello juarta 

     

    I don't really get what you need. could you make some example whats your input and what you need to have. In Power Query there are lot of possibilities to check a text, to split, rearrange and unite again

     

    Jimmy

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, juarta 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may add a new step with the followind codes to get the data whose format is like 'ABC123'.

    = Table.SelectRows(#"Changed Type",each
let 
data = Text.Trim([Data]),
len = Text.Length(data),
flag1 = List.MatchesAll( Text.ToList( Text.Start(data,3) ),each List.Contains({"a".."z","A".."Z"},_)),
flag2 = List.MatchesAll( Text.ToList( Text.End(data,3)  ),each List.Contains({"0".."9"},_))
in
len=6 and flag1 and flag2
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.