cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skhalatian Regular Visitor
Regular Visitor

M query function issue (loop)

I am trying to loop throu a function which I have done in some other project but for some reason I'm getting the following error 

 

Formula.Firewall: Query 'Dim - States / Cities' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

it's a function that I can pass State and City and it extract the price of the properties based on the regions in that city. output format is XML. I tested the function directly and it works fine when I invoke it. however when I try to use invoke custom fuction on a table it doesn't

 

let Test=(State as text, City as text) =>
    let
        Statein= State,
        Cityin= City,

    Source = Xml.Tables(Web.Contents("http://www.zillow.com/webservice/GetRegionChildren.htm?zws-id=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&state=" & Statein & "&city=" & Cityin & "&childtype=neighborhood")),
    Table0 = Source{0}[Table],
    Table2 = Table0{2}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table2,{{"subregiontype", type text}}),
    #"Expanded list" = Table.ExpandTableColumn(#"Changed Type", "list", {"count", "region"}, {"list.count", "list.region"}),
    #"Expanded list.region" = Table.ExpandTableColumn(#"Expanded list", "list.region", {"id", "name", "zindex", "url", "latitude", "longitude"}, {"list.region.id", "list.region.name", "list.region.zindex", "list.region.url", "list.region.latitude", "list.region.longitude"}),
    #"Expanded list.region.zindex" = Table.ExpandTableColumn(#"Expanded list.region", "list.region.zindex", {"Element:Text", "Attribute:currency"}, {"list.region.zindex.Element:Text", "list.region.zindex.Attribute:currency"}),
    #"Expanded region" = Table.ExpandTableColumn(#"Expanded list.region.zindex", "region", {"latitude", "longitude"}, {"region.latitude", "region.longitude"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded region",{"list.count", "list.region.id", "subregiontype", "list.region.url"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"list.region.zindex.Element:Text"] <> null))
in 
	#"Filtered Rows"
in 
Test

table that I'm trying to use the function has got the followinig format

 

image.png

 

and I map the function input columns  like following

 

image.png

 

Thanks is advance

Shaun

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: M query function issue (loop)

I had a go at using your code and ran into a few different problems.  Firstly, if I passed NY as the state and Holtsville as the city, the table I got back had a message:  "Error: invalid or missing ZWSID parameter" with a code of 2.  Hope that means something to you.

 

I also had an issue with the navigation rows in your code where you convert Source into Table2.  This is likely related to the error message I got back.  I couldn't go any further as "subregiontype" didn't exist in the returned table.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Highlighted
Super User
Super User

Re: M query function issue (loop)

A simple fix is to turn off the Privacy settings from File\Options and SEttings\Options.  Turn off the global and "this workbook".  Be aware that you wont have this feature to "protect you".  Personally, I prefer to have it off.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Baskar Super Contributor
Super Contributor

Re: M query function issue (loop)

@skhalatian did you resolved this issue.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,194 guests
Please welcome our newest community members: