Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hola queridos expertos comunitarios,
Tengo dos tablas que necesito anexar en Power Query. El problema es que una de esas tablas tiene una columna culculada que se crea con la siguiente expresión Dax:
id_city = IF(SEARCH ( "Dresden", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Augsburg", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Hannover", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "muenchen", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Leipzig", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Hamburg", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Wiesbaden", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "nuernberg", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Berlin", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "bochum", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "desden", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Sächsische-Zeitung", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "OneFootball", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "Spotify", CMAds[CM Campaign (CM Model)],, 0 ) = 0, IF ( SEARCH ( "akut recruiting", CMAds[CM Campaign (CM Model)],, 0 ) = 0, "14","1"),"1"),"1"),"1"),"6"),"7"),"1"),"10"),"11"), "5"),"2"),"4"), "3"), "8"),
"6"
)
Me preguntaba si hay una manera fácil de volver a crear esta función en la fase de transformación de datos (en Power Query), con el fin de tenerla disponible para anexar?
Muchas gracias de antemano!
Hola, @Saro90
Puede crear una columna personalizada con los siguientes códigos en 'Editor de consultas' para ver si ayuda.
=
if
Text.PositionOf([CM Campaign (CM Model)], "Dresden",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Dresden",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Augsburg",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Hannover",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "muenchen",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Leipzig",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Hamburg",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Wiesbaden",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "nuernberg",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Berlin",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "bochum",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "desden",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Sächsische-Zeitung",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "OneFootball",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "Spotify",Occurrence.First)=-1
then if
Text.PositionOf([CM Campaign (CM Model)], "akut recruiting",Occurrence.First)=-1
then "14"
else "1"
else "1"
else "1"
else "1"
else "6"
else "7"
else "1"
else "10"
else "11"
else "5"
else "2"
else "4"
else "3"
else "8"
else "6"
Saludos
Allan
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
@Saro90 , En la consulta de energía que tiene si entonces otra vez
y Text.Contains
https://docs.microsoft.com/en-us/powerquery-m/text-contains
Como
si Text.Contains([CM Campaign (CM Model)],"Dresden") y luego <> else <>
Hey @Saro90,
por desgracia, no hay una manera fácil de transformar el DAX de una columna calculada en una columna personalizada en Power Query.
saludos
Tom