Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to 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())
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.
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())
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 |
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
Proud to be a Super User!
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
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
88 | |
73 | |
64 | |
60 |