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...
- 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 :smileyhappy: )