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

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
SteveCampbell Established Member
Established Member

Re: New user: add custom column that queries data from Web Content API

First, make a new parameter called NPI:
PARAMETER.gif

 

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
Annotation 2019-09-10 154640.png

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

Annotation 2019-09-10 155745.png

 

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

 

Annotation 2019-09-10 160144.png

 

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!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

View solution in original post

banks334 Frequent Visitor
Frequent Visitor

Re: New user: add custom column that queries data from Web Content API

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 ?

 

https://ibb.co/bXv92g0

https://ibb.co/w4Dwv8m

 

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?

View solution in original post

6 REPLIES 6
SteveCampbell Established Member
Established Member

Re: New user: add custom column that queries data from Web Content API

First, make a new parameter called NPI:
PARAMETER.gif

 

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
Annotation 2019-09-10 154640.png

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

Annotation 2019-09-10 155745.png

 

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

 

Annotation 2019-09-10 160144.png

 

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!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

View solution in original post

banks334 Frequent Visitor
Frequent Visitor

Re: New user: add custom column that queries data from Web Content API

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 ?

 

https://ibb.co/bXv92g0

https://ibb.co/w4Dwv8m

 

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?

View solution in original post

SteveCampbell Established Member
Established Member

Re: New user: add custom column that queries data from Web Content API

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.

 

banks334 Frequent Visitor
Frequent Visitor

Re: New user: add custom column that queries data from Web Content API

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

SteveCampbell Established Member
Established Member

Re: New user: add custom column that queries data from Web Content API

Typing the word null actually will return a blank value. Your function returns a table, so if you replace errors with"no value", this is text, which means some rows have tables and some have text. Mixing data types in the same column is bad and you cannot expand anymore as the column isn't all tables. null doesn't have a data type as it is empty, so it doesn't affect the column. You can also use try ... otherwise as error handling, but that is a little more complicated and requires editing some M code.

 

If you question has been answered please mark the original one as the solution so others can find it easy (and kudos are always appreciated Smiley Happy )

 

banks334 Frequent Visitor
Frequent Visitor

Re: New user: add custom column that queries data from Web Content API

First, thank you so much for your help. Much appreciated. Query works well.

 

I have another request though. Now that I am returning the Enumeration type from the API, I have been asked to also return the name. Once I convert the "list" to a table I then need to convert "basic" to an additional table nested within the first table. I can't seem to figure out how to return these two different pieces of information. or, even more complicated I need to return three pieces of information so that the name can be in last, first format instead of the API "name" row that does not have the comma.

 

I could create a new "name" function but that would double the run time. It already takes a few minutes to refresh as is. That also doesn't solve the issue of combining the "first" and "last" row together instead of just returning "name" which has no delimiters. This is not the solution.

 

I know my current function is returning a table, or record?, so I know it's possible. I guess I just need to figure out how to return a table than can be expanded for both name and enumeration type. Any help would be appreciated. Thanks in advance!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 257 members 2,680 guests
Please welcome our newest community members: