Connect Data to other Data structures
In order to be able to use the account-related data, e.g. from the ERP system SPIRE, in a special data structure in Power BI, an assignment of account numbers to the report categories must be made once. I have created an interactive converter to ensure that no entry is forgotten in the large number of existing accounts and to minimize the possibility of errors.
The one- time procedure for configuring the financial report is as follows:
Export the accounts in SPIRE
Open this export file once in Excel, save it and then import it into the conversion program
Each line in the various reports has its own key and, of course, its own name
A conversion table must now be filled in to document which accounts belong to which report line.
So that no accounts are forgotten and other formal errors are avoided, the assignment can be made system-supported under the section “Accounts”:
Select whether to link for “Balance sheet” or “Income statement”
Use filter if necessary
Assign account to report category
If the system is too slow, select “Update later”
Finally, when “Update pending” shows “Yes”, install updates to the records
Export data in Menu
Menu
Export the SPIRE-Account data
Save it and open/save it in Excel before continuing
Check assigned fields
The list of Accounts
This list shows some already assigned Accounts
The target Report structure. Parameters define how the Report is presented.
If the button "Show" appears, a click on it will show the assigned Accounts
Accumulated categories are shown
The Ledger-Mapping with some Accounts assigned
The green color marks teh already assigned Accounts (when Update is applied)
Select the Report page the Account belongs to
Select from all possible Categories
Enter keyword to filter (before having left the filter-field)
The filter applied
Select the Report category
If a refresh after every entry slows down the system too much, the update/refresh can be stopped until the "Update later" is set to "No" again.