Summary

This guide goes through the step by step process of transforming a Datev GDPdU file into a format suitable for ingestion into MindBridge.

Notes/Prerequisites

Typically DATEV data is provided within a GDPdU package. You can learn more about what a GDPdU package is and how to unpack it in our article Formatting GDPdU: How do GDPdU Files Work?

Once you've unpacked the GDPdU folder, export the following two files from the File Manager:

• kontobuchungen
• debitorenkreditorenstammendaten

In this formatting exercise, we'll be utilising Microsoft Excel Power Query. 

Transform the data

1. First, we need to change a setting within Power Query. Open a new instance of Excel. Navigate to the “Data” tab, open the drop-down menu “Get Data”, and select “Query Options”.
   
   
   
   Under the “Data Load” section, select “Never detect column types and headers for unstructured sources” and click OK. This ensures that we have full control over the data types when importing files into Power Query.
   
   
   
   
2. Next, we need to import our files into Power Query so we can begin the transformation. On the “Data” tab, select “From Text/CSV”.
   
   
   
   Navigate to the location of your Kontobuchungen file, select it, and click “Import”. Select “Transform Data” in the dialogue box that opens. This will open the Power Query editor window.
   
   On the left, you’ll see a query has been created named after your file. Right click in this pane and navigate New Query > File > Text/CSV. Locate your DebitorenKreditorenStammendaten file, select it, and click “Import”. Select “OK” in the dialogue box that opens.
   
   
   
   
3. Now that we have both files imported into Power Query, we can start to format the data. The first major step we need to take is merging the two files together. Select the Kontobuchungen query by clicking on it in the Queries pane on the left. Next, we need to use the first row as the headers of the data. On the “Home” tab, select “Use First Row as Headers”.
   
   
   
   You will notice that this creates a step in the “Applied Steps” pane on the right. Repeat this process for the DebitorenKreditorenStammendaten query.
   
   The DebitorenKreditorenStammendaten file has a lot of columns that we don’t need, so we will remove some to reduce the size of the merged file. Select the DebitorenKreditorenStammendaten query on the left, and on the “Home” tab, click “Choose Columns”. Untick “Select All Columns” and tick “PKKtonr” and “Geschaeftspartner-Typ”, then click “OK”.
   
   
   
   We should then be left with only two columns, representing the GL account number and a D or K account indicator. With those steps taken, we can now perform the merge. Select the Kontobuchungen query and click “Merge Queries” on the “Home” tab.
   
   
   
   This will bring up the Merge dialogue box. Here, we need to select the second query we want to merge, in this case DebitorenKreditorenStammendaten, and we also need to select a column that has shared data between the two queries. Select Ktonr in the first table, and PKKtonr in the second table. We then need to select “Left Outer” as the join kind, which will take all results from the first table, and matching results from the second. Click “OK” to merge the two queries.
   
   
   
   An extra column has now been added to our Kontobuchungen query. Expand this column by selecting the expand icon in the column header. Untick “Use original column name as prefix” and click “OK”.
   
   
   
   
4. We can now begin the process of removing the rows of data that aren’t necessary for the analysis. Navigate to the “Kennung_EB” column. This column indicates opening balance entries, which need to be removed from the ledger. Click the filter drop down in the header and untick “1”. Click “OK” to filter this column.
   
   
   
   Navigate to the “Kontobewegungstyp” column and untick “2” from the filter drop down. Navigate to the “BereichsId” column and untick “2” from the filter drop down. If it has a warning stating "List may be incomplete" make sure to click "Load more" before clicking "OK"
   
   
   
   
5. Now that the unnecessary rows have been removed, we can look to utilise the columns we added earlier to identify the D & K account entries and take an appropriate action.
   
   
   Note: It’s not always clear whether these entries should be removed or rolled up into summary accounts. It’s a good idea to check with your client to see if they have D & K summary accounts and find out what they are, as the formatting process differs slightly depending on the results.
   
   In cases where there are no summary accounts, it's likely the D & K entries will need to be removed. Navigate to the "Geschaeftspartner-Typ" column, select the filter drop down, and deselect the D and K values.
   
   
   
   In cases where there are summary accounts, we need to alter the D & K entries to those accounts. In this example, all D entries need to be changed to account 1200, and all K entries need to be changed to account 3300. To do this, we can use the custom column feature in Power Query. Navigate to the “Add Column” tab and select “Custom Column”
   
   
   
   This will open the Custom Column window. Here we can name our new column “Account ID” and use the following formula to change our Ktonr according to the presence of a D or K in column Geschaeftspartner-Typ:
   

   if [#"Geschaeftspartner-Typ"] = "D" then "1200" 
   
   else if [#"Geschaeftspartner-Typ"] = "K" then "3300" 
   
   else [Ktonr] 

   
   
   
   This will create a new column which can be mapped as the Account ID in MindBridge.
   
   
6. We can also create an Amount column which can help us perform manual completeness checks on the data. To create an amount column, we can again use the Custom Column feature. Select “Custom Column” from the “Add Column” tab. Name the column “Amount” and use the following formula to calculate the amount:
   
   

   [Umsatz_Soll]-[Umsatz_Haben] 

   
   Then click “OK” to create the new column.
   
   
   
   
7. Lastly, we need to load the query into an Excel table, where we can copy the data to another workbook. Click “Close and Load” on the “Home” tab.
   
   
   
   This will load two tabs. One containing the DebitorenKreditorenStammendaten file, which we can now disregard, and the other containing the formatted Kontobuchungen. Select the Kontobuchungen tab and highlight all the data by pressing Ctrl + Shift + End. We can then copy the selected data by pressing Ctrl + C.
   
   Open a new Excel workbook, right click in cell A1 and select “Paste Special”. This will bring up a dialogue box. Select “Values and number formats” and click “OK”.
   
   
   
   We can then save this workbook ready to perform a completeness check, or upload to MindBridge. The following Knowledge Base article contains further details on performing a manual completeness check Formatting GDPdU: Running a Pre-Analysis Completeness Check