cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Add query parameter as column

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

4 REPLIES 4
Frequent Visitor

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

@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.

Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Post Prodigy
Post Prodigy

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors