Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Tesy
Helper I
Helper I

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 ) - SEARCH ("Horas de participación" , W2 ) - 26 ) )

 

Pero no lo estoy logrando, me pueden ayudar en corregirla?

1 ACCEPTED SOLUTION

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())

vzhangtinmsft_0-1736494566372.png

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.

View solution in original post

12 REPLIES 12
v-zhangtin-msft
Community Support
Community Support

Hi, @Tesy 

 

Can you provide some of the example data? And what you expect the output to be. Please remove any sensitive data in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

 

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.

Hola,

 

Funcionó algo las 2 lineas que te compartí?

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.

 

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

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())

vzhangtinmsft_0-1736494566372.png

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.

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!  

Muchas gracias @v-zhangtin-msft , 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 !

Muchas gracias, adjunto 2 lineas de ejemplo:

ID del módulo: - XXXX | 5 | Finanzas aplicado a ventas;Elige el tipo de servicio: - U4O NACIONAL- ORGANIZACIONES;Programa: - Escuela de Ventas Destilería Orendain;Grado: - XXXXXXXX;Horas de participación: - 8;Evaluación global: - 9 (Factor 3.7);Fecha de inicio: - 06/11/24;Fecha de fin: - 27/11/24;Monto a pagar (SIN IVA): - XXXXX
ID del módulo: - | XX | Coach 1: sesión individual (5 per;Nombre de Proyecto/Programa/Tema: - Desarrollo de XXXXXXXX XXXXX;Nivel: - XXXXXXXX (XXXXX);Número de horas: - 5;Fecha de inicio: - 25/11/24;Fecha de fin: - 29/11/24;Monto a pagar (SIN IVA) - XXXX

Hi, @Tesy 

 

Can you present your data in a table?

 

Best Regards

ryan_mayu
Super User
Super User

@Tesy 

you can try to create a column 

 =MID ( [COLUMNNAME], SEARCH ( "Horas de participación" , [COLUMNNAME]) + 26 , SEARCH ( ";Evaluación global", [COLUMNNAME]) - SEARCH ("Horas de participación" , [COLUMNNAME]) - 26 ) 

 

if this does not work, pls provide the error message or provide some sample data and expected output





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hola @ryan_mayu , muchas gracias por la ayuda, si me arrojo un mensaje:

Tesy_0-1736182210125.png

 

@Tesy 

you add  the third parameter in the search function which is like SEARCH (xxx,xxx, 0)

 

https://learn.microsoft.com/en-us/dax/search-function-dax?wt.mc_id=DP-MVP-5004616

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.