Error In PBI PQ

[AlanTheFirst]

Hello there,

I have been trying to pull out the latitude and longitude after following the instructions given in this video, which can be viewed on YouTube. Everything works well until I reach the invoke function part.

My table reflects the below-highlighted error instead of a table expansion column. When I view the error, the following appears: "An error occurred in the ‘FindGeoCode’ query. Expression.Error: A cyclic reference was encountered during evaluation."

TIA 🙏

[v-junyant-msft]

Hi @AlanTheFirst ,

"An error occurred in the ‘FindGeoCode’ query. Expression.Error: A cyclic reference was encountered during evaluation."
This error message usually means that a function or step in the query inadvertently referenced itself when trying to call a custom function, thus creating an infinite loop resulting in an error.
When calling a function, make sure that you are not passing the entire table or the column containing the function itself. And make sure that the function does not reference itself or return any steps of the function.
Verify that any steps in the query all have the same name as the function, and rename any steps or variables that may conflict with the function name.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlanTheFirst]

Hi Dino Tau,

Thank you for your response.

I am, however, struggling to understand where I should limit the function from not passing the entire table or the column containing the function itself.

Please walk me through the steps of what I should do and where I should do it. Please keep in mind that I have reversed all the steps that I did, and I'm now left with Query "FindGeoCode," which, if I click on "Invoke," I get the error.

Below is a screenshot reflecting my current screen before the error:

[lbendlin]

please show a sanitized version of the entire Power Query code.

[AlanTheFirst]

Sure, please see the code below:

= (Location) =>

let
Source = Xml.Tables(Web.Contents("API"&Location&"?o=xml&key=DTF3mf84IyZ29LewdMuz~jNg-nQQECp1k4wQ0Hljoxw~ArDjQ8vSeyUN_9FNxViUKI8elw4-QhaMsI2xg-L22eMnzB7Vwzte1kZ2TpoLHM2z")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
#"Expanded ResourceSets" = Table.ExpandTableColumn(#"Changed Type", "ResourceSets", {"ResourceSet"}, {"ResourceSets.ResourceSet"}),
#"ResourceSets ResourceSet" = #"Expanded ResourceSets"{0}[ResourceSets.ResourceSet],
#"Changed Type1" = Table.TransformColumnTypes(#"ResourceSets ResourceSet",{{"EstimatedTotal", Int64.Type}}),
Resources = #"Changed Type1"{0}[Resources],
Location = Resources{0}[Location],
#"Changed Type2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
Point = #"Changed Type2"{0}[Point],
#"Changed Type3" = Table.TransformColumnTypes(Point,{{"Latitude", type number}, {"Longitude", type number}})
in
#"Changed Type3"

[lbendlin]

Location = Resources{0}[Location],

You are defining "Location"  twice, once as a parameter in the function and once as a step name.

NOTE: Please do not share your API keys on the interwebs.

NOTE: Please read about the Query and RelativePath parameters for Web.Contents. Web.Contents - PowerQuery M | Microsoft Learn  you code can be much improved:

(Location) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/",[RelativePath = Location, Query = [o="xml", key=Key]])),
    Location1 = Source{0}[ResourceSets]{0}[ResourceSet]{0}[Resources]{0}[Location],
    #"Expanded GeocodePoint" = Table.ExpandTableColumn(Location1, "GeocodePoint", {"Latitude", "Longitude"}, {"Latitude", "Longitude"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded GeocodePoint",{"Name", "Latitude", "Longitude"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Latitude", type number}, {"Longitude", type number}})
in
    #"Changed Type"