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.
Hello togehter from Germany,
i work since some weeks with Powerbi and its a nice tool. Today i have a prob with my first custom function.
let get_sublocation = (street as text, housenr as text, postcode as text, city as text) = > let Quelle = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?address="&street&&housenr&","&postcode&&" "&city&","&key=xyz")), results = Quelle[results], #"In Tabelle konvertiert" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"address_components", "formatted_address", "geometry", "place_id", "plus_code", "types"}, {"Column1.address_components", "Column1.formatted_address", "Column1.geometry", "Column1.place_id", "Column1.plus_code", "Column1.types"}), #"Erweiterte Column1.geometry" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.geometry", {"location", "location_type", "viewport"}, {"Column1.geometry.location", "Column1.geometry.location_type", "Column1.geometry.viewport"}), #"Erweiterte Column1.types" = Table.ExpandListColumn(#"Erweiterte Column1.geometry", "Column1.types"), #"Erweiterte Column1.geometry.viewport" = Table.ExpandRecordColumn(#"Erweiterte Column1.types", "Column1.geometry.viewport", {"northeast", "southwest"}, {"Column1.geometry.viewport.northeast", "Column1.geometry.viewport.southwest"}), #"Erweiterte Column1.geometry.location" = Table.ExpandRecordColumn(#"Erweiterte Column1.geometry.viewport", "Column1.geometry.location", {"lat", "lng"}, {"Column1.geometry.location.lat", "Column1.geometry.location.lng"}), #"Erweiterte Column1.address_components" = Table.ExpandListColumn(#"Erweiterte Column1.geometry.location", "Column1.address_components"), #"Erweiterte Column1.address_components1" = Table.ExpandRecordColumn(#"Erweiterte Column1.address_components", "Column1.address_components", {"long_name", "short_name", "types"}, {"Column1.address_components.long_name", "Column1.address_components.short_name", "Column1.address_components.types"}), #"Erweiterte Column1.address_components.types" = Table.ExpandListColumn(#"Erweiterte Column1.address_components1", "Column1.address_components.types"), #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Column1.address_components.types",{"Column1.address_components.long_name", "Column1.geometry.viewport.northeast", "Column1.geometry.viewport.southwest", "Column1.geometry.location_type", "Column1.geometry.location.lng", "Column1.geometry.location.lat", "Column1.place_id", "Column1.plus_code", "Column1.types"}), #"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte Spalten", each ([Column1.address_components.types] <> "administrative_area_level_1" and [Column1.address_components.types] <> "administrative_area_level_2" and [Column1.address_components.types] <> "political" and [Column1.address_components.types] <> "sublocality_level_1")), #"Entfernte Spalten1" = Table.RemoveColumns(#"Gefilterte Zeilen",{"Column1.formatted_address"}), #"Transponierte Tabelle" = Table.Transpose(#"Entfernte Spalten1"), #"Umgekehrte Zeilen" = Table.ReverseRows(#"Transponierte Tabelle"), #"Höher gestufte Header" = Table.PromoteHeaders(#"Umgekehrte Zeilen", [PromoteAllScalars=true]), #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"street_number", Int64.Type}, {"route", type text}, {"sublocality", type text}, {"locality", type text}, {"country", type text}, {"postal_code", Int64.Type}}) in get_sublocation
The Engine said "Expected Token: RightParen." When i click on Show
it marked the first line (the commas between the variables)
get_sublocation = (street as text, housenr as text, postcode as text, city as text)
Can anyone tell me a right function definiation? I have test something like ; as seperator, but no one works.
Thanks a lot
Pascal
Solved! Go to Solution.
Hi @Anonymous,
Based on my tets, you could modify your code like this:
(street as text,house as text, postcode as text, city as text)=> let Quelle = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?address="&street&"housenr&"&"postcode&"&"&city&","&key=xyz")), results = Quelle[results], #"In Tabelle konvertiert" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"address_components", "formatted_address", "geometry", "place_id", "plus_code", "types"}, {"Column1.address_components", "Column1.formatted_address", "Column1.geometry", "Column1.place_id", "Column1.plus_code", "Column1.types"}), #"Erweiterte Column1.geometry" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.geometry", {"location", "location_type", "viewport"}, {"Column1.geometry.location", "Column1.geometry.location_type", "Column1.geometry.viewport"}), #"Erweiterte Column1.types" = Table.ExpandListColumn(#"Erweiterte Column1.geometry", "Column1.types"), #"Erweiterte Column1.geometry.viewport" = Table.ExpandRecordColumn(#"Erweiterte Column1.types", "Column1.geometry.viewport", {"northeast", "southwest"}, {"Column1.geometry.viewport.northeast", "Column1.geometry.viewport.southwest"}), #"Erweiterte Column1.geometry.location" = Table.ExpandRecordColumn(#"Erweiterte Column1.geometry.viewport", "Column1.geometry.location", {"lat", "lng"}, {"Column1.geometry.location.lat", "Column1.geometry.location.lng"}), #"Erweiterte Column1.address_components" = Table.ExpandListColumn(#"Erweiterte Column1.geometry.location", "Column1.address_components"), #"Erweiterte Column1.address_components1" = Table.ExpandRecordColumn(#"Erweiterte Column1.address_components", "Column1.address_components", {"long_name", "short_name", "types"}, {"Column1.address_components.long_name", "Column1.address_components.short_name", "Column1.address_components.types"}), #"Erweiterte Column1.address_components.types" = Table.ExpandListColumn(#"Erweiterte Column1.address_components1", "Column1.address_components.types"), #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Column1.address_components.types",{"Column1.address_components.long_name", "Column1.geometry.viewport.northeast", "Column1.geometry.viewport.southwest", "Column1.geometry.location_type", "Column1.geometry.location.lng", "Column1.geometry.location.lat", "Column1.place_id", "Column1.plus_code", "Column1.types"}), #"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte Spalten", each ([Column1.address_components.types] <> "administrative_area_level_1" and [Column1.address_components.types] <> "administrative_area_level_2" and [Column1.address_components.types] <> "political" and [Column1.address_components.types] <> "sublocality_level_1")), #"Entfernte Spalten1" = Table.RemoveColumns(#"Gefilterte Zeilen",{"Column1.formatted_address"}), #"Transponierte Tabelle" = Table.Transpose(#"Entfernte Spalten1"), #"Umgekehrte Zeilen" = Table.ReverseRows(#"Transponierte Tabelle"), #"Höher gestufte Header" = Table.PromoteHeaders(#"Umgekehrte Zeilen", [PromoteAllScalars=true]), #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"street_number", Int64.Type}, {"route", type text}, {"sublocality", type text}, {"locality", type text}, {"country", type text}, {"postal_code", Int64.Type}}) in Quelle
Result:
You could also download the pbix file to have a view.
https://www.dropbox.com/s/swiwxog7njyola8/Error%20in%20Custom%20function%20definition.pbix?dl=0
Regards,
Daniel He
I have found the problem... it was the space between "= >"
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |