Forum Discussion
New user: add custom column that queries data from Web Content API
First, make a new parameter called NPI:
Make sure you add a valid NPI number, this will be needed for testing. Also, make sure you set it as text - even through it's a number, it is going to be appended to a URL string.
Then, make a new query -
Select New Source > Web
Click advancedPaste in " https://npiregistry.cms.hhs.gov/api/?version=2.1&number= " to the first URL part, and have it as text (make sure you include the https://)
The second URL part, change to parameter, and select NPINow, you'll be returned a list. To get the enumeration_type , in this example:
- Click List
- Click Record
- Convert to Table
- Filter Name to enumeration_type
- Remove Name Column
Now, you can rename this query if you like. I called it NPIFunction.
You now have the query ready to convert to a funtion. To do this, simply right click on the query in the left pane, and select "Create Function".Now your code is ready to be reused!
In your query you want to add it to:- Go to Add Column
- Invoke Custom Function
- Select your Function
- Select "Column" under NPI then select the correct Column
Make sure you NPI column is text and not a number format
Now it will go row by row, and run the query. You can expand the column to get the result, by clicking the symbol next to the column name.
Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!Ok, that was great information! Next issue, I have "null" values in the NPI column. I do NOT want to remove rows because I will late rbe creating a new table of doctors that need to be reviewed by hand (nulls/errors). What is the typical method for exception handling? When I expand my custom column it stops at the first row containing a null value and throws an error. The query result in that column is an error.
I am used to working in Excel or ACL where I either use if statements in my logic to check for errors, or, when running macros there are exception handling functions. I am guessing I will have to go back and "edit" the function that was created to add the error checking? Something like this: https://docs.microsoft.com/en-us/power-query/handlingerrors ?
I mitigated the issue to some extent by changing all null values to zeroes. I no longer get the mismatch type exception converting null to text. However, I still have an issue with expanding the resulting table data that contains errors. Any advice on how to expand the column? I am guessing I need some kind of custom formula in that "step" in the query to ignore errors and expand the table as blank or something?
Memorable MemberFirst, make a new parameter called NPI:
Make sure you add a valid NPI number, this will be needed for testing. Also, make sure you set it as text - even through it's a number, it is going to be appended to a URL string.
Then, make a new query -
Select New Source > Web
Click advanced
Paste in " https://npiregistry.cms.hhs.gov/api/?version=2.1&number= " to the first URL part, and have it as text (make sure you include the https://)
The second URL part, change to parameter, and select NPI
Now, you'll be returned a list. To get the enumeration_type , in this example:
- Click List
- Click Record
- Convert to Table
- Filter Name to enumeration_type
- Remove Name Column
Now, you can rename this query if you like. I called it NPIFunction.
You now have the query ready to convert to a funtion. To do this, simply right click on the query in the left pane, and select "Create Function".
Now your code is ready to be reused!
In your query you want to add it to:
- Go to Add Column
- Invoke Custom Function
- Select your Function
- Select "Column" under NPI then select the correct Column
Make sure you NPI column is text and not a number format
Now it will go row by row, and run the query. You can expand the column to get the result, by clicking the symbol next to the column name.
Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!
Ok, that was great information! Next issue, I have "null" values in the NPI column. I do NOT want to remove rows because I will late rbe creating a new table of doctors that need to be reviewed by hand (nulls/errors). What is the typical method for exception handling? When I expand my custom column it stops at the first row containing a null value and throws an error. The query result in that column is an error.
I am used to working in Excel or ACL where I either use if statements in my logic to check for errors, or, when running macros there are exception handling functions. I am guessing I will have to go back and "edit" the function that was created to add the error checking? Something like this: https://docs.microsoft.com/en-us/power-query/handlingerrors ?
I mitigated the issue to some extent by changing all null values to zeroes. I no longer get the mismatch type exception converting null to text. However, I still have an issue with expanding the resulting table data that contains errors. Any advice on how to expand the column? I am guessing I need some kind of custom formula in that "step" in the query to ignore errors and expand the table as blank or something?
- SteveCampbell
The easiest way is to right click on the column > replace errors > type null.
Do this after you add the conditional column, and before you expand it.
Yes, I tried that actually. I've been playing with different solutions all day. I replaced all errors with "not found" and that ddin't work because the option to expand the column then dissapeared. I just tried "null" and that seems to have worked. Thank you! I guess I'll need ot do more reading so I understand why there is a difference there...
Hi SteveCampbell : I am trying to achieve something similar to this scenario. Step 1: I am trying to first get some pipeline notices info from an api and display those in a ‘table’ visual. This step is done. Step 2: get details info for a notice [another api]
There are two APIs (‘notice’ and ‘notice details’ ) I can connect to Api#1 [notice], which is my first data source [“Get Data” via ‘Web’ providing the api url and Api-Key]. I get 50 most recent notices. I get the data and display them in a ‘Table’ visual. One of the fields is ‘Id', an unique field for each notice. When I click on a notice in the ‘Table’ visual, I want to see the ‘notice details’ depending on the 'Id'.
Api#2 [notice details] is what I need to connect to now. ‘Id’ is a required field for this Api#2 to get details for a specific notice [with that id]. Now, When I click on a specific notice on the table visual, I have the Id for that notice. I have added drill-through page with another ‘Table’ visual to display the ‘notice details’ info.
I followed your steps and did the following:
-Manage parameter-> New parameter: NoticeId & provided a real value
-get new source-> Web-> Advanced-> URL parts : https://api.XYZ.com/natgas/events/v1/notices/details?ids=
Selected the parameter: NoticeId
Add part: &format=csv
Also provided the api-key and could see a preview of the ‘notice details’ info that the api returned for the specific id I provided earlier for ‘NoticeId’ parameter. The data looks good.
I named the query, ‘NoticeDetails’, right click-> Create Function-> named it ‘NoticeDetailsFunction’
I then selected the ‘NoticeDetails’ query, ‘Add Column’, ‘Invoke Custom Function’ and did the following:
After selecting the function, I chose ‘Column name’ & NOT text for NoticeId [3rd line] and chose ‘Id’ the field that specifies a notice id that I need to get ‘notice details’ .
But I get error for this :
I am kind of not sure what to do from this point. 1. To get rid of this error. 2. How to get ‘notice details’ for a specific id? 3. the api#2 returns various fields, I am interested to grab the ‘body’ field which actually contains the ‘details’ info for a notice.
Much appreciated in advance. Best, ~Sohana. 🙂
Hi SteveCampbell : I got the solution.
-I had to change the function in advance editor-> to change NoticeId type from text to number and use Number.toText(NoticeId) in the url.
-Also I copied my master data source into a 2nd table, renamed it and kept only Id Column. Then selected this table to -'Add Column', 'Invoke Custom Function' step to bring a table of records for each row/ Id on this 2nd table. After expanding the Column-> I selected which fields I need to show up in this table.
-I then added a 'Notice Details' page, added drill-through feature with 'Id', had a 'Table' visual to show info from the 2nd table [giving me the info form my api#2].
-Now I can drill-through on 'Id' from my first page/ Notice Summary: for a specific row/Id-> I go to the 'Notice Details' page and see the Details for that Notice!
Voila! Thanks a lot for the hints. 🙂
SteveCampbell banks334 Thanks for bringing a solution to this matter. It really helps.
When i invoke the column based on the function created, i dont get the value that im requesting. Instead, i get "Table" as the returned value.
What am I doing wrong?
Thank you!
Hi Anonymous: Check my step 2 -> you need to expand the Table [Click on 2 arrows beside the Column] and then select the Columns you need! Cheers!
JensKlökerHelper I
I have a very similar scenario. As of now I implemented it in the way shown here. However, if I publish the dataset to the service, there can't be set up a scheduled refresh. Power BI is complaining about dynamic data sources, which are described here:
https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-sourcesAs soon as I don't use parameters in the first statement of the Web.Contents(), the warning "Some data sources may not be listed because of hand-authored queries." disappears.
Is there some clever trick?