Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Power query : do action when a function is successfull

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 😢

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • foodd's avatar
    foodd
    Community Champion
    3 years ago

    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-Forum/ba-p/963216

    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.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    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 🙂 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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