Help with converting DAX to M in most efficient way

[bikelley]

Hello, 

I have an M query that needs to changes, but I am not familiar with M. 

The issue is this query relying a lot on [Region]. I want to minimize the use of this column. I want to leverage the [Account.Region_Detail] column and  [Account.Region__c]. If both columns are blank then only use the [Region]. (Those 3 columns coming from 3 different tables)

Condition - If we used [Region] we have to change the value "META" = "iMETA" and "EMEA" = "EUR". 

For the DAX query I came up with,  please help me to add the condition as well. (my dax query is missing the condition, not sure how to add it in the best way) 

Region_New = IF(df_ProjectSummary[Account.Sub_Region__c] = "iMETA", "iMETA",
                IF(df_ProjectSummary[Account.Region__c] = "EMEA", "EUR",
                    IF(df_ProjectSummary[Account.Region__c] = "" && df_ProjectSummary[Account.Sub_Region__c] ="", df_ProjectSummary[Region],
                        df_ProjectSummary[Account.Region__c])))

Any help to convert this to M will really help full, and also please help me to add the condition as well in a most efficient way. 

Data looks like- 

Note - I tried editing this but I got few errors, this is the only one I captured, "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?".

Thank you 

[Greg_Deckler]

@bikelley Seems like maybe:

= Table.AddColumn(#"Replaced Value1", "Region Adj",
each if [Account.Sub_Region__c] = "iMETA" then "iMETA"
else if [Account.Region__c] = "EMEA" then "EUR"
else if [Account.Region__c] = "" && [Account.Sub_Region__c] ="" then [Region]
else [Account.Region__c])

[bikelley]

@Greg_Deckler 

Thank you so much for taking the time to look at my issue. I think your Power Query is good if I can add the condition. The issue that I am stuck in I also have to include the below condition when we using [Region] in the above Power Query. Because [Region] column has "META" and "EMEA", I need to change the name as below. 

Can you please help me to add the below condition to Power Query (the best and efficient way)? 

Contains([Region],"META") then "EUR"
Contains([Region],"EMEA") then "EUR"

 Again, thank you so much for your help and time. 

[Greg_Deckler · Accepted solution]

@bikelley Maybe:

= Table.AddColumn(#"Replaced Value1", "Region Adj",
each if [Account.Sub_Region__c] = "iMETA" then "iMETA"
else if [Account.Region__c] = "EMEA" then "EUR"
else if Text.Contains([Region],"META") or Text.Contains([Region],"EMEA") then "EUR"
else if [Account.Region__c] = "" and [Account.Sub_Region__c] ="" then [Region]
else [Account.Region__c])

[bikelley]

@Greg_Deckler Thank you so much. This works for me. I really appreciate your help. 

[bikelley]

@Greg_Deckler  Thank you so much, I think this should work, I will get back to you asap. After I check on this. Again, I truly appreciate your help. 

[m3tr01d]

Hi,
for this kind of thing, it is better to do it in Power Query, why do you need to do it in DAX?

What's the problem with your Power Query approach?

[bikelley]

I am not familiar with Power Query. I was running into so many errors. That is the issue.  Thank you so much