Forum Discussion

Tesy's avatar
Tesy
Helper I
1 year ago
Solved

Función MID

Hola comunidad,   Estoy tratando de crear esta formula que tengo en excel al Power BI =VALUE ( MID ( W2 , SEARCH ( "Horas de participación" , W2 ) + 26 , SEARCH ( ";Evaluación global", W2 ) - SEAR...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Tesy 

     

    You can try the following methods.

    Horas = 
VAR HorasPos = IFERROR(SEARCH("Horas de participación", 'Table'[COLUMNNAME]), 0)
VAR EvalPos = IFERROR(SEARCH(";Evaluación global", 'Table'[COLUMNNAME]), 0)
VAR IsPatternFound = (HorasPos > 0) && (EvalPos > HorasPos + 26)
VAR ExtractedText = 
    IF(
        IsPatternFound,
        MID('Table'[COLUMNNAME], HorasPos + 26, EvalPos - (HorasPos + 26)),
        BLANK()
    )
RETURN
    IF(IsPatternFound, VALUE(ExtractedText), BLANK())

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, Tesy 

 

The formatting of those two lines above is not clear enough. Can you provide a screenshot of the source data in Excel?

 

Best Regards,

Community Support Team _Charlotte

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

 

Tesy's avatar
Tesy
Helper I
1 year ago

Lo que hago con esas 2 lineas en excel es aplicar la siguiente formula:

=VALUE ( MID ( W2 , SEARCH ( "Horas de participación" , W2 ) + 26 , SEARCH ( ";Evaluación global", W2 ) - SEARCH ("Horas de participación" , W2 ) - 26 ) )

 

pero me falta algo al aplicarlo en Power BI, estoy batallando con la estructura de la formula

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Tesy 

     

    You can try the following methods.

    Horas = 
VAR HorasPos = IFERROR(SEARCH("Horas de participación", 'Table'[COLUMNNAME]), 0)
VAR EvalPos = IFERROR(SEARCH(";Evaluación global", 'Table'[COLUMNNAME]), 0)
VAR IsPatternFound = (HorasPos > 0) && (EvalPos > HorasPos + 26)
VAR ExtractedText = 
    IF(
        IsPatternFound,
        MID('Table'[COLUMNNAME], HorasPos + 26, EvalPos - (HorasPos + 26)),
        BLANK()
    )
RETURN
    IF(IsPatternFound, VALUE(ExtractedText), BLANK())

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • Tesy's avatar
      Tesy
      Helper I
      1 year ago

      It worked! I corrected some texts in my database hehe

      Can I take advantage of your knowledge and correct this adaptation for me?

      Horas =
      VAR HorasPos = IFERROR(SEARCH("Horas de participación", sheet1[Respuesta del formulario]), 0)
      VAR EvalPos = IFERROR(SEARCH(";Evaluación global", sheet1[Respuesta del formulario]), 0)
      VAR NumPos = IFERROR(SEARCH("Número de horas", sheet1[Respuesta del formulario]), 0)
      VAR Fini = IFERROR(SEARCH(";Fecha de inicio",sheet1[Respuesta del formulario]), 0)
      VAR IsPatternFound = (HorasPos > 0) && (EvalPos > HorasPos + 26) && (NumPos > 0) && (Fini > NumPos + 19)
      VAR ExtractedText =
      IF(
      IsPatternFound,
      MID(sheet1[Respuesta del formulario], HorasPos + 26, EvalPos - (HorasPos + 26)),
      IF(
      MID(sheet1[Respuesta del formulario], NumPos + 19, Fini - (NumPos + 18)),
      BLANK()
      ))
      RETURN
      IF(IsPatternFound, VALUE(ExtractedText), BLANK())

       

      Thank you very much in advance!  

    • Tesy's avatar
      Tesy
      Helper I
      1 year ago

      Muchas gracias Anonymous , suncionó muy bien con las filas correspondientes, pero al aplicarlo a toda la columna completa ya no funcionó, por otras variables, pero era lo que realmente esperaba!

       

      Gracias !