Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Connecting with DB through API

  • 1.  Connecting with DB through API

    Silver Contributor
    Posted Nov 09, 2019 01:55 AM
    Hi Team,

    Currently am using excel data to create my dashboard in power bi desk top.

    i would like connect my power bi desk top with CRM database by using API key.

    we are using leadsquared CRM for our business and they provided the API Key . please help me  to connect with database using API.



    ------------------------------
    ganesh kumar
    Business Analyst
    9500990994
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Connecting with DB through API

    Bronze Contributor
    Posted Nov 11, 2019 05:02 AM
    First of all, read the API documentation, because not allways they are going to answer the API call with data.
    If the connection is possible, I recomend to you to call the API and get the Data answer via JSON Document and manage that data in Dataflow scenario.
    PowerQuery can help you with that

    ------------------------------
    Walter Calcagno
    Founder
    953341272
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Connecting with DB through API

    Bronze Contributor
    Posted Nov 12, 2019 10:04 AM
    Hi Ganesh,

    If they don't have an existing connector, the first step would be to see if you can connect via the WebContents method, short of writing a custom one:
    https://docs.microsoft.com/en-us/powerquery-m/web-contents

    To use it, you will need to enter the connection credentials in your data source settings.  The documentation is lacking. The parameters must be passed in as an object. Here's an example I wrote recently to connect to the SynopticLabs weather API, which returns a table of time series weather station data. You'll note this is written as a function. No need to do that, you can just omit the () => and it will run as a query. As stated, your API key will need to be entered into your connection settings. Some of the arguments in the WebContentsParams object are hard coded, some are referencing PBI parameters. Naturally, the tokens / keys / arguments will all differ with the API to which you are connecting. But, this should give a template to follow.

    //Returns a time series of readings for a given station from Synoptic api.
    //https://developers.synopticdata.com/mesonet/v2/stations/timeseries/
    () =>
    let
        StartDate = DateTime.ToText(API_StartDate,"yyyyMMddHHmm"),
        EndDate = DateTime.ToText(API_EndDate,"yyyyMMddHHmm"),
        WebContentsParams = [
            ApiKeyName = "token",
            RelativePath = API_TimeSeriesURL,
            Query = [
                token = API_Token, //Saved as parameter. 
                //stid = Stids, //List of stations passed to function.
                state = "UT",
                country = "US",
                county = "Salt Lake",
                fields = "STID,NAME,LATITUDE,LONGITUDE,ELEVATION,STATE", // List of metadata attributes to include. If omitted, returns all. 
                vars = "air_temp,dew_point_temperature,relative_humidity,wind_speed,wind_direction,wind_gust,altimeter,pressure", //Only return variables needed.
                //https://developers.synopticdata.com/mesonet/v2/api-variables/
                obtimezone = "local", //return times in timezone of the station. 
                units = "english", // return units in US system.
                //radius = Lat & "," & Long & "," & Rad //
                start = StartDate,
                end = EndDate
                //recent = Text.From(API_RecentMinutes) //Only supports up to one month / 43200 minutes 
    
            ]
        ],
        APIBinaryQueryResults = Web.Contents(API_BaseURL, WebContentsParams),
        APIQueryTextResults = Text.FromBinary(APIBinaryQueryResults),
        APIQueryResultLines = Lines.FromBinary(APIBinaryQueryResults),
        APIQueryResultsTable = Table.FromColumns({APIQueryResultLines})
    in
        APIQueryResultsTable​

    Good luck. Let me know if you have any questions and I'll be happy to help.

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    West Valley City UT
    (801) 878-3363
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Connecting with DB through API

    Silver Contributor
    Posted 30 days ago
    Hi Ryan,

    Thank so much for given more information API connection, let me try this and if i have any doubt i will come back..  thanks again .

    ------------------------------
    ganesh kumar
    Business Analyst
    9500990994
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Connecting with DB through API

    Silver Contributor
    Posted 30 days ago
    Thank You Walter for the info...

    ------------------------------
    ganesh kumar
    Business Analyst
    9500990994
    ------------------------------

    Conference-PBI_200x200