Answered by:
M-Language: Splitting parameter required to invoke function (separating API key from remaining REST API URL)

Question
-
I wrote a function which automatically cleans up data that I am pulling via REST API call.
(apicall) => let Source = Json.Document(Web.Contents(apicall)), results = Source[results], #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"utc_offset", "venue", "rsvp_limit", "headcount", "visibility", "waitlist_count", "created", "rating", "maybe_rsvp_count", "description", "event_url", "yes_rsvp_count", "name", "id", "time", "updated", "group", "status"}, {"utc_offset", "venue", "rsvp_limit", "headcount", "visibility", "waitlist_count", "created", "rating", "maybe_rsvp_count", "description", "event_url", "yes_rsvp_count", "name", "id", "time", "updated", "group", "status"}), #"Expanded venue" = Table.ExpandRecordColumn(#"Expanded Column1", "venue", {"country", "city", "name"}, {"country", "city", "name.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded venue",{"utc_offset", "rsvp_limit", "headcount", "visibility", "waitlist_count", "created", "rating", "maybe_rsvp_count"}), #"Expanded group" = Table.ExpandRecordColumn(#"Removed Columns", "group", {"name"}, {"name.2"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded group",{"updated", "id"}), #"Divided Column" = Table.TransformColumns(#"Removed Columns1", {{"time", each _ / 1000, type number}}), #"Removed Columns2" = Table.RemoveColumns(#"Divided Column",{"status"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"name", "yes_rsvp_count", "time", "name.2", "country", "city", "name.1", "description", "event_url"}), #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns",{"description"}), #"Added Custom" = Table.AddColumn(#"Removed Columns3", "Venue Date", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[time])), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Venue Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Venue Date", Order.Descending}}), #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows",{"Venue Date", "name", "yes_rsvp_count", "time", "name.2", "country", "city", "name.1", "event_url"}), #"Removed Columns4" = Table.RemoveColumns(#"Reordered Columns1",{"time"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns4",{{"name", "Venue Name"}, {"yes_rsvp_count", "Yes RSVP Count"}, {"name.2", "Gropup Name"}, {"country", "Country"}, {"city", "City"}, {"name.1", "Host"}, {"event_url", "Venue URL"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Venue Name", type text}, {"Yes RSVP Count", Int64.Type}, {"Gropup Name", type text}, {"Country", type text}, {"City", type text}, {"Host", type text}, {"Venue URL", type text}}) in #"Changed Type1"
All I need to enter the REST API URL as a paramter, and all works well.
Currently, I am entering the entire REST API URL including the API key:
https://api.meetup.com/2/events?status=past&group_urlname=Big-Data-Developers-in-Berlin&key=0123456789abc
(above just an exemplary, invalid API key)
I was wondering though how to tweak the function that in case I generate a new API key, I would only need to enter the key instead of the entire REST API URL. Any help appreciated. Thanks!
Rafael Knuth
- Edited by Rafael Knuth Friday, March 2, 2018 10:04 AM
Friday, March 2, 2018 9:49 AM
Answers
-
You can either do this by appending the apicall parameter to the rest of the string, or do it a bit more properly and use Uri.BuildQueryString for this purpose. Here is what you need to change for Source assignment
Source = Json.Document(Web.Contents( "https://api.meetup.com/2/events?"& Uri.BuildQueryString([ status= "past", group_urlname = "Big-Data-Developers-in-Berlin", key = apicall]) ))
- Marked as answer by Rafael Knuth Friday, March 2, 2018 12:36 PM
Friday, March 2, 2018 12:33 PM
All replies
-
You can either do this by appending the apicall parameter to the rest of the string, or do it a bit more properly and use Uri.BuildQueryString for this purpose. Here is what you need to change for Source assignment
Source = Json.Document(Web.Contents( "https://api.meetup.com/2/events?"& Uri.BuildQueryString([ status= "past", group_urlname = "Big-Data-Developers-in-Berlin", key = apicall]) ))
- Marked as answer by Rafael Knuth Friday, March 2, 2018 12:36 PM
Friday, March 2, 2018 12:33 PM -
For additional free resources, you can find this session that Chris Webb did back in the day that goes a bit deeper into how you can use the Web.Contents function.
The Web.Contents function has a lot of optional choices in its second argument that can help you make your query refresh on the service more efficiently, but it would be recommended that you create a custom connector if you need that.
Sunday, March 4, 2018 9:19 AM