Forum Discussion
New user: add custom column that queries data from Web Content API
Hi, I am new to power BI and need some help with a project. I thought I was trying to do something pretty straightforward, but, I've come to realize it's a bit more complicated. I am trying to add a custom column of data to a table that provides a specific piece of data from an API query. The function/code needs to be dynamic in that each cell in the column will return a value based upon the referenced column of "lookup" criteria.
More specifically, I am working with an imported table of doctor names and npi numbers. I want to query the NPPES, national provider registry, for each doctors npi number and then have this custom column show the enumeration type for each npi number (NPI-1 or NPI-2).
Here is the API I am working with: https://npiregistry.cms.hhs.gov/registry/help-api
This is the oversimplified formula that I came up with and results in an error:
"Json.Document(Web.Contents("npiregistry.cms.hhs.gov/api/?version=2.1&number=" & [NPI] ))"
https://ibb.co/nz586py
https://ibb.co/vLyXWcQ
I am guessing I need to write a custom function that queries the web content api. The function would then also format the returned nested JSON table to return only the information in the table that I want? Not sure where to begin with this in PowerBI. Can anyone point me in the right direction with how to implement this? possibly provide an example to follow? Thank you in advance!
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?
11 Replies
- SteveCampbell
Memorable Member
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?
- 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.
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!
