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
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
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
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.
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
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.....
Sorry amend above, the function is on the table "Claims" not "Premium" and the field in the claims table is called "Loss Postcode Abbrev"
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |