Pagination with the Qoders Exact Connector
- Robert Sep
- 1 jul 2020
- 1 minuten om te lezen
This is a follow-up blog on how to use the Qoders Community Power BI connector for Exact. You can download the connector here: https://github.com/Robert1976/PBI-Custom-Connectors
So how do you use the connector? In this example we will create a function in Power Query that retrieves all General Ledgers from Exact and uses pagination. Let's dive in! First go to Power Query and create a new blank query.
Now name your query. In my case I start my Query with 'fn'. This way I know I am dealing with a Power Query Function.
Now open your query in the "Advanced Editor" screen and copy-paste the code from the attached "fnGetGeneralLedgersByYear.txt"-file. Now click "Done". You now have a function with 2 parameters: selecteddivision and year. The function returns a table.
You can easily call this query from other Power Query sources. Just create another blank query, name it "CombineGeneralLedgersLast3Years". Open the "Advanced Editor" and copy and paste from the corresponding attached file. Do not forget to change your division id in the Query! (3 times!) Now click 'Done' and you will see that this Query loads the General Ledgers from the last 3 years.
Some notes:
The line "Source = qoders.powerbi.connector.exact.Feed(url)" in the "fnGetGeneralLedgersByYear" function is the actual Qoders Exact Connector. You can change the Url parameter with anything you like to retrieve other data from Exact.
The "fnGetGeneralLedgersByYear" function is a pagination enabled function. It will keep retrieving data from Exact in iterations, and add these iterations to a list until all data is retrieved. You can re-use this pattern for other Exact data or even other sources.
Comentarios