Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everybody,
I'm new in Power BI and I have to use Power query to check if a custom function returns successfully or not.
I have some addresses, and I call a Bing maps function to get latitude and longitude coordinates. Sometimes this function fail because address has wrong format.
I would like to know which address is ok and which is wron, with error or success in a column to indicate the result of the search.
Here is the formula to add latitude and longitude columns (it works) :
= Table.ExpandTableColumn(Table.AddColumn(#"Colonne fusionnée insérée", "LatitudeLongitude", each try fxPointLookup(Text.Trim(Text.Replace([Address], "&", " and "))) otherwise
try fxPointLookup(Text.Trim(Text.Replace([City], "&", " and "))) otherwise fxPointLookup(Text.Trim(Text.Replace([COUNTRY], "&", " and "))) ), "LatitudeLongitude", {"Latitude", "Longitude"}, {"Latitude", "Longitude"})
So, now, I would like to add a new column, called "Error", to display "True" if the fxPointLookup call (fxPointLookup(Text.Trim(Text.Replace([Address], "&", " and ")))) failed and "False" if not.
Could you help me to do this please ?
I don't know how to get the "success" case, or a default case 😢
Solved! Go to Solution.
Hi @Syndicate_Admin ,
Please try:
= Table.AddColumn(
Table.ExpandTableColumn(
Table.AddColumn(
#"Colonne fusionnée insérée",
"LatitudeLongitude",
each try fxPointLookup(Text.Trim(Text.Replace([Address], "&", " and ")))
otherwise try fxPointLookup(Text.Trim(Text.Replace([City], "&", " and ")))
otherwise fxPointLookup(Text.Trim(Text.Replace([COUNTRY], "&", " and ")))
),
"LatitudeLongitude",
{"Latitude", "Longitude"},
{"Latitude", "Longitude"}
),
"Error",
each let
addressResult = try fxPointLookup(Text.Trim(Text.Replace([Address], "&", " and "))),
cityResult = try fxPointLookup(Text.Trim(Text.Replace([City], "&", " and "))),
countryResult = try fxPointLookup(Text.Trim(Text.Replace([COUNTRY], "&", " and ")))
in
addressResult[HasError] and cityResult[HasError] and countryResult[HasError]
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thanks, It works perfectly 🙂
Hi,
Here is a PowerBI file with sample.
You have to set a Bing Maps API Dev Key in fxPointLookup function (key="YourAPIKey").
In ADDRESS table, column ERROR is a sample of the result I want to obtain dynamically, with the success or fail of calling to fxPointLookup. If fxPointLookup returns some coordinates, I want FALSE. If not (Error of function), I want TRUE.
Thanks for your help 🙂
Hi @Syndicate_Admin ,
Please try:
= Table.AddColumn(
Table.ExpandTableColumn(
Table.AddColumn(
#"Colonne fusionnée insérée",
"LatitudeLongitude",
each try fxPointLookup(Text.Trim(Text.Replace([Address], "&", " and ")))
otherwise try fxPointLookup(Text.Trim(Text.Replace([City], "&", " and ")))
otherwise fxPointLookup(Text.Trim(Text.Replace([COUNTRY], "&", " and ")))
),
"LatitudeLongitude",
{"Latitude", "Longitude"},
{"Latitude", "Longitude"}
),
"Error",
each let
addressResult = try fxPointLookup(Text.Trim(Text.Replace([Address], "&", " and "))),
cityResult = try fxPointLookup(Text.Trim(Text.Replace([City], "&", " and "))),
countryResult = try fxPointLookup(Text.Trim(Text.Replace([COUNTRY], "&", " and ")))
in
addressResult[HasError] and cityResult[HasError] and countryResult[HasError]
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.