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

[banks334]

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!

[SteveCampbell · Accepted solution]

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

Stay up to date on  
Read my blogs on  

[SteveCampbell · Accepted solution]

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

Stay up to date on  
Read my blogs on  

[JensKlöker]

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

As 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?

[EdwinRiofrio]

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

[sohananahid]

Hi @EdwinRiofrio: 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!

[sohananahid]

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

[sohananahid]

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

[banks334 · Accepted solution]

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?

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

[banks334]

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]

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  )

[banks334]

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!