Questions? We have answers.

Data Formatting Guide: Automate repetitive tasks in Excel Power Query

  • Updated

Summary

Learn a few common formatting problems associated with general ledger files and how to automate the formatting process using Excel Power Query.

In this article we will use the Journal export from QuickBooks Desktop, however many of the steps taken will be applicable to other accounting software exports.


Use cases

If you have many general ledger files from the same accounting software that are all in the same format, and require transformation before ingestion into MindBridge, then Excel Power Query could be the perfect tool for you.

Once a file has been transformed to an ingestible format and the steps taken have been recorded, you will be able to transform any file in the same format instantly!


The problem

Below is an example of the formatting of a QuickBooks Desktop Journal report, taken from our extraction guide.

mceclip2.png

The issues preventing this file from being ingested by MindBridge are:

  1. The data is grouped by transaction; the Trans#, Type, Entered/Last Modified, Last Modified by, Date and Num columns need to have their data populated down to each row of the transaction.
  2. Trans# isn't a mandatory field within QuickBooks, hence not all transactions have a Trans#.
  3. The account number and name occupy the same field.
  4. There are extraneous rows, such as totals.

The solution

  1. Populate the empty rows with the relevant data from each column using the Fill Down function.
  2. When populating the data, ensure that the blank Trans# fields (for example, cells B5 and B27) are maintained and not populated with incorrect data using a Custom Column.
  3. Split the account number and name into separate columns using the Split Column by Delimiter function.
  4. Filter out the blanks on a column that contains data on every entry row.


Get and transform data

In order to start transforming the data, we first need to import the data into Power Query. There are a number of ways to do this depending on the file type.

  1. Open either a new worksheet, or your general ledger file in Excel.
  2. Navigate to the Data ribbon at the top. Within the Get & Transform Data section, you will find a number of options to get the data depending on whether your data is in your open worksheet, or a separate file.
  3. Select the appropriate method for the data you have. In this example, we will select From Table/Range since our data is within our open worksheet. Select a cell within your data and Excel will then attempt to detect the data present and convert it into a table. Take care to ensure that all of your data is in this table and resize appropriately in the Create Table pop-up.
    If you want to import a .txt or .csv file, simply select From Text/CSV and select Transform Data from the preview window that pops up.
    Get___Transform_Data.gif

  4. Now that the data has been loaded into Power Query we can start the transformation process. Along the top ribbons there are numerous ways to manipulate the data. In the right pane titled Query Settings we can see the Properties and Applied Steps. In Power Query, when you take an action to manipulate the data, a step is automatically created and can be viewed in this pane. These recorded steps are the principle behind the automation.
  5. We can name our query in the Name box. You'll see that a few steps have automatically been applied. The Change Type step has been applied to all columns; Excel is essentially trying to guess what data is in each column and has assigned it a type.
    We'll need to remove this step as we want the option to select data types manually rather than have them automatically assigned. To do this, select the cross icon next to the step.
    mceclip1.png

    We are now ready to begin manipulating the data!


Populate the data using fill down

We can begin transforming the data by populating the empty rows within the following columns:

  • Trans#
  • Type
  • Entered/Last Modified
  • Last Modified by
  • Date
  • Num

In this example, we know that the Date is always present on the first row of a transaction group, however Trans# may sometimes be blank.

  1. Start by creating a custom column for the transaction number, taking into account we need to preserve the rows where the transaction number is blank. Navigate to the Add Column ribbon and select Create Custom Column. When the custom column window comes up we'll name it Transaction ID.
    mceclip2.png

  2. if [#"Trans #"] = null and [Date] <> null then "@" else
    if [#"Trans #"] <> null and [Date] <> null then [#"Trans #"] else null

    This is the formula we will use to make our custom column.
    The logic behind this formula is to check a row for data present in both the Trans# and Date columns. It's worth noting that the value "null" represents a blank cell in Power Query. If the transaction number cell is blank, and the date is not blank, we want to output a special character. in this case an "@" symbol. If the Trans# and the Date cells both contain values, then we output the value in the Trans# cell. If both the Trans# and Date cells are blank, then we output null.

    mceclip2.png

  3. The Available columns section on the right enables you to insert column names into the Custom column formula box to easily create the custom formula referencing your own data. The custom column creator will automatically detect any syntax errors to help you get to the right solution. Once the formula has been filled in, select Ok to generate your new custom column.
  4. The custom column Transaction ID has now been created which populated either the transaction number or a special character on the first line of each transaction. Right-click on the Transaction ID column header and select Fill>Down to populate the null values with the values above.
  5. We can now use Replace Values to find this special characters, in this case "@", and replace them with null. This will preserve those rows with blank transaction ID's whilst filling down the necessary data.
    Fill_down.gif

  6. With this logic in place, we can now create custom columns for:
    • Type
    • Entered/Last Modified
    • Last Modified by
  7. We can open Create Custom Column again and copy paste the same formula, substituting the appropriate columns.


Splitting columns by delimiter

Next we can solve the fact that the account number and account name occupy the same column. In this example the number and name are separated by a delimiter. Using the Split Columns by Delimiter function in Power Query we can easily split these values into two separate columns.

  1. Right-click on the column heading and select Split Column > By Delimiter.
    This will bring up a window with a few options. Power Query will try to detect the delimiter automatically and give a suggested delimiter. If it is incorrect, you can select a different delimiter from the menu or enter a custom delimiter. You can also select the position and the occurrence of the split under the Split At section.
    mceclip0.gif

  2. Select OK once you are happy with the configuration.
    Power Query will split the column into separate account number and account name columns. 
  3. You will notice that Power Query tries to add a Change Type step which will detect what type of data is in the new columns and try to automatically assign it a type.
    Again, we will want to remove this step as it can lead to issues with account numbers that have leading zeros.
  4. We can rename these newly created columns by double-clicking the assigned name in the column header. In this example, they have been named Account ID and Account Description in order to match up with the MindBridge columns.


Cleaning up the data

With the major changes now completed, we can focus on smaller tasks such as rearranging the columns, deleting any extraneous data, changing the data types and making sure the data conforms to the standards that enable it to be imported into MindBridge.

  1. A few columns have been replaced by the custom columns created earlier in the process. You can delete any of these columns by right-clicking on the Column Header and selecting Remove:
    • Trans#
    • Type
    • Entered/Last Modified
    • Last Modified by
    • Date
    • Num 
  2. You can rearrange the columns as needed by selecting and dragging the column header left or right to the desired position.
  3. You can change the type of data in each column by selecting the Type icon in each column header. It's important to make sure...
    • The Date columns are use the Date format
    • Entered Date column uses a Date/Time format
    • Credit/Debit/Amount columns uses a Decimal Number format
    • We recommend maintaining the Transaction ID column as text, as converting it to a number format will remove any preceding zeros in the ID.
  4. Filter out the blanks in the Account ID column to ensure the rows containing extraneous data, such as totals, are removed.
  5. Use the Replace Values tool once again to replace the "null" values in Debits/Credits with "0".


Loading data and future use

Once the data is in the correct format and the steps have been recorded, we can close and load the data into an Excel sheet.

Select Close & Load in the top left. A new sheet will be created with a table populated with the formatted data. You can now copy and paste the data into a new workbook and import this into MindBridge.

mceclip0.png

With the query now created, you can utilize the steps generated for any set of data that is in the same format, i.e., it has the same column names, column position and number of columns. There are 2 ways to load a new dataset into the query:

  1. If you used Get Data: From Table/Range, simply delete the old data from the table and paste new data in. Once your new data is in place, navigate to the data ribbon and select Refresh All.
    mceclip0.png

  2. If you used Get Data: From File, edit the source of the query from the Applied Steps pane:
    mceclip1.png


Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.

Was this article helpful?