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.
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
and I map the function input columns like following
Thanks is advance
Shaun
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.
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.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |