Showing results for 
Search instead for 
Did you mean: 
RafaelKnuth Regular Visitor
Regular Visitor

M-Language: Splitting parameter in function (separating API key from remaining REST API URL)

I wrote a function which automatically cleans up data that I am pulling via REST API call.


(apicall) =>

    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}})
    #"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:

(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!




v-huizhn-msft Super Contributor
Super Contributor

Re: M-Language: Splitting parameter in function (separating API key from remaining REST API URL)