Connect to an API without writing code!

Five minutes after reading this blog, you’ll be able to dismiss all the developers on your team and connect to an API without any programming knowledge. OK, maybe don’t fire your team, but you’ll really be able to connect to an API, transform the data, visualize it, and work with it in your Dynamics 365 environment — or create a PowerApp — without writing a single line of code!

To start you’ll need:

As a first step, sign in to PowerApps and navigate to the environment that you want to start with. Under Data, select Dataflows and click on New dataflow:

In the next screen we start to see where the magic of dataflows happen. Type in the API information as below:

Since this is a free online API, there’s no need for authentication or on-premise data gateway. So after clicking next, you’ll see the Power Query editor. But wait, this doesn’t look like the data at all! This is because Power Query doesn’t automatically recognize the data as a table. Instead, it defaults to a list of records. To convert this list of records to a table, you must select the “Convert to table” button:

In the pop up, leave the default values and click on OK. Now, you’ll be able to select which columns of your data you want to integrate into your dataflow by clicking on the arrow in the column header:

And voila! You now have access to the data from your API. Here, we can change the column names and do any kind of transformation we need (joining other tables, setting column types, creating calculated columns, adding/removing rows, etc.) But we’re not done yet. Our goal is to visualize and work with this data from a Power App, so click on Next to continue the process. Next, dataflows allows us to write the API data into a CDS entity. In this scenario, I’ll select to create a new one.

The Hound’s Best Practice: Always select an existing entity that you’ve created before to adhere to solution management best practices (you never want to have a custom entity with the Default CDS Publisher for example!).

Update the field types for all the columns and ensure your Primary Name field is of type “Text”. Selecting the option to “Delete rows that no longer exist in the query output” will delete the records from the CDS entity that no longer exist in the source API (for example, if the data has been deleted in the source API):

After clicking on Next, you’ll have the option to schedule a refresh down to the minute. This means how often do you want the dataflow to pull data from the API. Click on Create and in a couple of seconds the entity and the data will be populated in your CDS environment:

Now that your data is in your CDS environment, you can do anything with it! Display it in a Model Drive App, connect it to other services with Flow, do reporting with Power BI, etc. In this case, I created a simple canvas app that allows me to look up dog breeds and look at the details (YouTube video below):

For the last couple of years we’ve seen Microsoft push their vision of no-code/low-code business applications over and over again and they’ve been extremely successful at achieving it. With all the tools of the Power Platform, a business no longer needs to spend hundreds or thousands of hours developing custom applications. Instead, they are empowering business users to take ownership of their day-to-day applications and helping system integrators provide value to their customers at a much faster pace.

As a disclaimer, the role of developers and technical architects will always be needed at the enterprise level. It’s never recommended to make significant changes (like connecting to an API with dataflows) in a production environment as the consequences could be catastrophic!

Related posts:

In the world of software engineering, the roles of Site Reliability Engineering (SRE) and DevOps often intersect, but they are not necessarily mutually exclusive. While there may be some similarities…

It was a terribly rainy day and I was out and about in those rains. I found myself standing in front of the Cube, wondering how and what do I observe for my collection of 10 today. I would have liked…

Petonic Infotech is a premier business management consulting firm located in Delhi, India. They provide comprehensive services to help businesses optimize their operations and maximize their profits…