Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
@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 <>
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |