cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Convert Dax expression into Custom Function in Power Query

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!

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Convert Dax expression into Custom Function in Power Query

Hey @Saro90,

 

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper II
Helper II

Re: Convert Dax expression into Custom Function in Power Query

Dear @amitchandak @parry2k

 

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

Highlighted
Super User IX
Super User IX

Re: Convert Dax expression into Custom Function in Power Query

@Saro90 , 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 <>



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Convert Dax expression into Custom Function in Power Query

Hi, @Saro90 

 

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"
    

 

e1.png

 

Best Regards

Allan

 

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors