LOOKUPVALUE in M

[Captainarw]

Hi

I have this code in M when trying to add a new column with a LOOKUP, which is constantly saying "Token Comma Expected":

#"Added Conditional Column1" = Table.AddColumn(#"Changed Type4", "Custom", each if Text.StartsWith([Loss Location Postcode Abbrev], "1") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "2") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "3") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "4") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "5") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "6") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "7") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "8") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "9") then "USA- In Transit" else LOOKUPVALUE(#"Canadian Postal Codes"[Place Name],#"Canadian Postal Codes"[Postal Code],#"Claims"[Loss Location Postcode Abbrev])

Can anyone kindly assist please??

Many thanks

[AlB]

@Captainarw 

Table.SelectRows(#"Canadian Postal Codes", (inner)=>inner[Postal Code] = [Loss Location Postcode Abbrev])[Place Name]{0}

If this does not work I'd need the pbix (with dummy data if necessary, reproducing the issue) to come up with an accurate solution

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Captainarw]

Hi Thanks for your reply. Apologies, I am new to Power BI. When I add that in I get this:

#"Added Conditional Column1" = Table.AddColumn(#"Changed Type4", "Custom", each if Text.StartsWith([Loss Location Postcode Abbrev], "1") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "2") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "3") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "4") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "5") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "6") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "7") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "8") then "USA- In Transit" else if Text.StartsWith([Loss Location Postcode Abbrev], "9") then "USA- In Transit" else Text.SelectRows(#"Canadian Postal Codes", (inner)=>inner[Postal Code] = [Loss Location Postcode Abbrev])[Place Name]{0})

Now I am getting an error message "Expression.Error: The name 'Text.SelectRows' wasn't recognized. Make sure it's spelled correctly". Could you kindly offer some support?

Thank you.

[AlB]

Hi @Captainarw 

LOOKUPVALUE is not M, it is DAX. Try

Instead of

LOOKUPVALUE(#"Canadian Postal Codes"[Place Name],#"Canadian Postal Codes"[Postal Code],#"Claims"[Loss Location Postcode Abbrev])

try

Table.SelectRows(#"Canadian Postal Codes", (inner)=>inner[Postal Code] = [Loss Location Postcode Abbrev])[Place Name]{0}

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Captainarw]

Hi, sorry I tried this and it didn't work see asbove. Am I missing something? Just to confirm the end result is the [Place Name] in the table "Candian Postal Codes" and I am performing the funtion on the table "Premium", thanks..... 

[Captainarw]

Sorry amend above, the function is on the table "Claims" not "Premium" and the field in the claims table is called "Loss Postcode Abbrev"