How can I use all of my Exact Online divisions with Invantive Bridge and PowerBI?

Question

I'm using the Invantive Bridge ADO.NET provider to get my Exact Online data into Microsoft PowerBI.

This is the query I currently use:

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=ExactOnlineAll;apiUrl=https://start.exactonline.nl;"
             , "select * from exactonlinerest..journals"
             )
in
    Source

This gives me the data from the default selected division in Exact Online. I would however like to be able to query not just the default division, but a selection or all of my divisions.

Answer 1

You can either select all divisions or a selection of your divisions in your query as following:

Use all

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=ExactOnlineAll;apiUrl=https://start.exactonline.nl;"
             , "use all;select * from exactonlinerest..journals"
             )
in
    Source

Which will look like this in the Advanced Editor:

---

Use a selection of divisions

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=ExactOnlineAll;apiUrl=https://start.exactonline.nl;"
             , "use 823645, 103786, 878904;select * from exactonlinerest..journals"
             )
in
    Source

Which will look like this in the Advanced Editor:

Answer 2

When you use the Custom Connectors (see), you can not easily edit the query. In that case you can specify the SQL statements upon connection establishment to set the right companies as shown in picture below:

Brute force is

use all

When you run for an accountant with thousands of companies, that might take a while :-)

To select some companies, comma-separated:

use 123123, 2342343

To select all companies with a specific name:

use select code from systemdivisions where name like '%Albert Heijn%'