Add query parameter as column

[ptmuldoon]

Can someone maybe help show/explain if it is possible to auto create a column with the parameter value that is part of json query?

I'm still new at this and have something like this as part of my source:

api_base_url = "https://xxx.mysite.net/XXX?year=2020&begmonth=1&eyear=2020&endmonth=11&datasetid=-3",

There is more to url, etc. But I would like to try and place that "-3" into a new column. Currently, I am running separate queries for each datasetid I need which gives me separate/multiple tables. I then add the column to each table and then do a merge.

But I'm hoping to auto add that column based on the parameter being passed in the url.

Thanks,

PT

[ptmuldoon]

Aplogies for taking so long to respond.

@ToddChitt   Yes, I believe you understood what I was trying to explain.  Currently I run separate URL queries each with a different datasetID number.   All the tables return the exact same with columns such as Account Number, Year, Month, Amt.   But the tables to not include a column for the datasetID number.

So currently, I am adding that DataSetID column manually and then adding the number to it., ie, -3, 1, 2, etc.  And then after that doing an Append to bring all the tables together into 1 table.   

I'm hoping to have the column automatically added when I run the query.   It sounds like I need to look in trying to create sometype of Custom function.  Any help on that would be greatly appreciated.

Thank you,

PT

[ToddChitt]

@ptmuldoon Try this:

Get yourself a dataset with a single column, let's call it datasetid. It can be manually entered data at this point.

Now create a function in Power Query/M code that looks like this:

(DataSetID) =>

let

Source = Web.Contents("https://.......&datasetid=" & #[DataSetID]

in

Source

(This is exactly what @mahoneypat suggested, I'm just expounding on it.)

Note that Power Query is case sensitive. If you name the parameter "DataSetID" int he first line, you need to refer to it as #[DataSetID] in the third line.

Test out the function by clicking on it, and entering a value for the parameter DataSetID. You should get back a dataset. You can delete this resulting dataset if you want. 

Now on your simple table of one column, add a column with the "Invoke Custom Function". Use the function you created, and use the column to supply the input parameter that the function wants. Now comes the fun part: in the new column, click the icon in the column header to have it list the columns available. Pick the ones you want added to THIS dataset. 

You can add rows to the Dataset ID table to get more pages from you web call.

Hope this helps.

[mahoneypat]

If you have a table with a column called DatasetID, you can use this expression in a custom column to get the data for multiple ids in one query (maybe need to add your authentication too).  You can then expand the column with all the resulting tables.

= Web.Contents("https://xxx.mysite.net/XXX?year=2020&begmonth=1&eyear=2020&endmonth=11&datasetid=" & [DatasetID]),

Regards,

Pat

[ToddChitt]

Not sure I understand your issue. Might it be that you run the URL to get multiple tables by supplying a different number at the end for the "&datasetid=-3" portion of the URL? Are you then MANUALLY merging those datasets into one?

Do all the datasets have the same meta-data (in other words, are you doing an APPEND or UNION of the results)?

If so, I suggest you look into writing your own custom FUNCTION in Power Query, then calling that function in a table/column that lists all of the Datasets to be merged.

If the answer to the first questions above is YES, then we can maybe help you write such a function.