• Register
Welcome to Kodlogs, programming questions and answer website.
0 votes
POST data to REST API URL in Power BI (Use JSON / XML ODBC Drivers for Web API and Files)
by (320 points)  
reshown by

1 Answer

0 votes
Hi guys and welcome to ZappySys
ODBC PowerPack 101. We continuewhere we left off. In the previous video
triplet authenticating using OAuthpaginating through the results and then
using queries in applications. Today inaddition to making HTTP GET requests we
are going to POST data to a REST APIusing ZappySys ODBC JSON driver and then
use the results in a power bi report solet's imagine that I have these
companies in the same hub spot seeonline crm tool and I want to have a
Power BI report where I could search bythe main name and get all the companies
that are related to that domain let'ssay Boeing or Netflix comm so here let
me bring the report.I already made so I have thisparameter domain which I could select
and as you see this there are alreadythe main names here listed so basically
these are retrieved from hotspot API andso let's let's say once I select at the
main I want here to have a table withall the company names and next to them I
want to have a phone number so we let meshow you the queries I also built so
basically here is the parameter and ituses the query domains I have this
domains list which is basically thecompany's query and then I just remove
the columns and get this single columnlist and the companies is the query to
ODBC queryI have hotspot companies let me show you
thatHubSpot companies here so as you see
data here I will be making a preview andwill get yes companies same companies
hereHubSpot widely test etc yeah and then I
have this dummy dummy query will justselects the current domain parameter
value so that I can use it in thiswhat's that name card yeah okay yeah so
I guess we can start and you know thinkhow to get those company names based on
the domain name okay so the first thingI had to do is go to API documentation
and find this request I have to make sothis time this is post you have the URL
and then this column tells that this isthe parameter of a domain which you want
to get companies off right so we areready to create a new data source
let's get bring back the data sourceadministrator let's add a new data
source I will name in HubSpot search forcompanies right yeah okay so now I am
ready to copy the URL so the sample URLso this sample URL will retrieve all the
companies of hotspot as you see here andthis time let me show you how to
authenticate using a simple URLparameter and HubSpot uses this h api
key so let me go back to my accountwhere I where I have my company's
company is listed as you see from theprevious video we have this integration
and integration apps registered hereshowing that we are using them and we
can disconnect the usage and now let'spress this HubSpot api key copy it and
use it here okay let's go back here todocumentation and there are also several
parameters we need to pass like limitproperties of set so let's again take
the sample data fromhere and let's set the request method to
post because it's here post let'sconfigure limit we can remove the limit
because we don't want to limit thecompany names that will be retrieved for
us and these properties the property isan array of of properties we want to be
returned for us so the main will bereturned then I will change this to
phone then name will be retrieved for usand I don't need this one and this is
offset for paging which we will not beusing now okay we also have to set the
body content type and it says hereapplication JSON this is kind of
important option because if you will useany other kind most probably the you
will you won't get the response you wantyou will get an error yeah and also in
this place we could enter the parameterslet's say you know any my header my
value and this could be passed as wellbut but none of them are required to be
passed so we are not going to be to bepassing any you can also modify them
using this button in this editor so whatyou enter here will appear here so let
me delete those and let me show youanother way of authenticating this HTTP
connection type you basically use itwhen you have windows credentials
windows authentication or basic ntlmkerberos
or when you are making soap requests sowhen making soap requests you should use
then ZappySys xml driver and use thisauthentication type now there is also
static token authentication where youjust write the header name and here and
the key here it's and also there isdynamic token where you have to login
into the website and retrieve the tokenand then it can be passed along all with
other requests okay so we won't be usingany now okay let's make an let's test
connection let's make a previewcompany's results let's write results so
that this area this column would be putin two rows okay in only one row because
HubSpot has only one company so thedomain name was retrieved and company
name and also somewhere will be phonenumber okay okay so we are ready to use
this query in power bi so let's let meremove all the unnecessary options I'm
copying this clicking oh okay gettingback to power bi and let's create a new
data source from ODBC let's connectlet's select hotspot search for
company's Advanced Options and now let'senter the sequel statement okay let's
click OKload okay let's open query editor and
let me rename this search for companiesand rename the columns I want to use I
will rename this to phone this tocompany okay so as you see we got back
the results of HubSpot come to a closeand apply report okay now I can include
the company name and phone okay so itkind of works but not yet we still have
to make this query parametric so when weselect the parameter the disco this
table updates as well okay so yeah let'sselect to Boeing now and go back to
query editor and now we will make thisquery dynamic okay let's right click on
the search for companies advanced editorand you scroll to the right and when you
find hot spot they remove it close thestring open the string now put two
ampersands and write down the parametername which is domain right here okay
done oh it's already retrieved us backthe data of Boeing okay close and applyvoila you have Boeing and you have all
the companies of Boeing we can check inhere to boeing.com boom aircraft engines
and the phone number is the same as herelet's select something else let's say
Netflix my changes okay you have hereNetflix companies as well together with
phones so that's it that's how youconfigure ODBC data source based on
ZappySys json driver to post data to arest api and use the response in
whatever application you wantwe just used power bi to demonstrate you
know the concept you also learn how tomake post requests dynamic by using
applications parameters and be awarethat ZappySys xml driver UI
is very similar to the episodes jsondrivers UI so you can set up data source
based on it and then get xml filesinstead of json files from urls or call
soap web service methods in the nextparts we will explore how to use the
pieces or the busy drivers in otherapplications thanks for watching