locked
M-Language: Splitting parameter required to invoke function (separating API key from remaining REST API URL) RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Friday, March 2, 2018 9:49 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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