Getting BC data with dataflows in Dataverse and back

There are several ways to get your data from Business Central into Dataverse:

• Synchronize option
  Synchronization and Data Integration – Business Central | Microsoft Docs
  With this option your data lives also in Dataverse. Also you need to write code if you want to sync tables that are not done by Microsoft (there are standard 9 tables synced).
  You can also extend the existing ones:
  Extending your DataVerse Sync in Business Central – Discover Microsoft Business Central (bertverbeek.nl)
• Virtual Tables
  Business Central and [!INCLUDE[cds_long_md](../includes/cds_long_md.md)] admin reference – Business Central | Microsoft Docs
  With this option you need to have Business Central Online. If you have an on-premises installation this option doesn’t work.
  

But there is also a third option (thanks to Stefan Strube | LinkedIn ). With this option you are using Dataflows inside Dataverse. You need also an API published on your Business Central environment.

If you go to Dataverse you see also the tab Dataflows:

If you create a new dataflow just enter the name of the new dataflow and choose “Web API” as source:

Then fill in your credentials and URL to your API. If that is done you get a Power Query with all your data:

If you go next you can choose if you want to create a new table or insert into an existing table:

In this case you can also enter the “Primary Key”. I always change the “Column Name”. Also here enter your “alternate key columns”.

Note: Your “Alternate Key Columns” must be the Primary Key of your Business Central table! Otherwise you can get duplicate date and for the upserts.

You can also add this later on the table itself:

After that you can set the refresh settings:

In this way your table is always up to date!

And after the Dataflow is kicked of you have got your Business Central data in Dataverse:

But how do you push you data back to Business Central?

Well in this case you can use Power Automate and the trigger “When a row is added, modified or deleted”:

Put the change type on “added” and just do a post to the API that you have called in your dataflow.

In this way you can put your data into the Dataverse table. After that is done the Power Automate flow push it to Business Central and when the dataflow is triggered again all the other values are populated into the Dataverse table.

Example

• Entering a new item in the dataverse table:

• Automate trigger:

• Business Central

• After a refresh of the Dataflow: