Convert Dax expression into Custom Function in Power Query

[Anonymous]

Hi dear Community experts, 

I have two tables which I need to append in Power Query. The issue is that one of those tables has a culculated column which is created with the following Dax expression:

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

I was wondering is there an easy way to recreate this function in data transformation stage (in Power Query), in order to have it available for appending?

Many thanks in advance!

[v-alq-msft]

Hi, @Anonymous 

You may create a custom column with the following codes in 'Query Editor' to see if it helps.

=
    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"
    

Best Regards

Allan

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

[amitchandak]

@Anonymous , In power query you have if then else

and Text.Contains

https://docs.microsoft.com/en-us/powerquery-m/text-contains

like

if Text.Contains([CM Campaign (CM Model)],"Dresden") then <> else <>

[Anonymous]

Dear @amitchandak @parry2k, 

will be very grateful if you could have a look on this. 

[TomMartens]

Hey @Anonymous,

unfortunately there is no easy way that transforms the DAX of a calculated column into a custom column in Power Query.

Regards,

Tom