We have released a version of Xero4PowerBI that enables you to incrementally refresh Xero journals in the PowerBI service. To complete this you will need to be able to download the Xero4PowerBIi.pbix file, you will need Power BI desktop, a license to use the Power BI service and a OdataLink account. The OdataLink account is available as a 30-day trial.
We are doing this is because we have been unable to do this directly from the version of Xero4PowerBI in App Source. This is not possible for a variety of technical reasons. There is also no practical way to download large volumes of journals directly from Xero.
What we have here is a version of Xero4PowerBI that will do incremental refresh of journals. This means that if you rely on analysis of journals to validate transaction, you can now access that data quickly and efficiently. Here is the download link and there is an accompanying YouTube video.
When you are ready the PBIX file and open it in Power BI desktop. We're going to publish an incremental report to a workspace, and I'll show you how it's configured.
The issue we have is that Xero accumulates all of your journals, and they take a long time and a lot of processing to download. To overcome the technical limitations, we use Odatalink, which provides value added access to Xero data. OdataLink track the creation date of journals and creates an archive of journals, so that thay can be accessed more readily. We use the creation date of the journal to enable standard Power BI incremental refresh.
We want to do an incremental refresh and that's achieved by setting some parameters which are set up in this report, and we have the Journals table configured to do it. The first step is open the Xero4PowerBIi.pbix file that you have downloaded in PowerBI desktop. From the menu select Transform data->Edit parameters. We are interested in three parameters:
Set the Odatalink parameter to you OdataLink data feed (this is how we change the data source), set the RangeStart to the date you want your journals to start and RangeEnd to today’s date. If you go to the tables view and right click on the Journals table and click ‘Incremental refresh’ you will see the setting:
Incrementally refresh this table
Archive data starting 3 Years before refresh date
Data imported from 01/01/2021 to 27/10/2024 (inclusive)
Incrementally refresh data starting
Data will be incrementally refreshed from 28/10/2024 to 29/10/2024 (inclusive)
Incrementally refresh data 2 Days before refresh date
Get the latest data in real time with DirectQuery (Premium only) Learn more
Selected table cannot be folded for DirectQuery.
Only refresh complete days
Now refresh your data from the menu and your journals will download to the desktop, this may take some time. Normally Xero4PowerBI just cuts off journals for the last 90 days.
The next thing is to publish to the service, select the workspace that you wish to use and publish.
One of the things about incremental refresh is that you have to refresh the data on the desktop first. because once it's in the service, the service takes over totally and takes over refreshing your data for you.
You can do up to 8 refreshes a day, it will default to a midnight refresh.
You, you now have a Power BI report that is incrementally refreshing your journals. We'll cover journal reporting in another blog.
Read more about incremental refresh
