Perhaps because it is so easily available to them, professionals have used Excel for years to prep data for importing with Transaction Pro. Today’s blog is about a newer way to accomplish what I call Data Wrangling – Power Query. If you already own Excel, you already own it; you just don’t know the Power you have to Query yet. Besides being a free feature in Excel, some of the other things I LOVE about using Power Query are:
- It allows you to build a repeatable process (especially relevant for preparing your data for Transaction Pro.)
- It does NOT change source data, instead, it creates new files;
- There is lots of support for it – thousands of articles and many, many books available to help with unique situations.
To start using this new powerful tool, you need to open an empty workbook in Excel and go to the Data tab.
Once inside that tab, on the far left you will see a get data button. This is where the magic begins. As you see in the menu, this powerful tool can get a large array of files and databases, and can even read PDF files!
To get you started, I am going to use a report that was exported out of QuickBooks into Excel. It doesn’t matter if it is the online or desktop version, but my specific example was from a desktop report. The goal here is to get a list of open invoices to import into a QuickBooks file.
As soon as I open the exported Excel file I can see that the tool has started putting the report into rows and columns. The first tool used to shape this data is the filter tool. We were careful to include all the columns we needed on the detail row when we created the QuickBooks report. As a result, if we simply filter column2 by clicking on the arrow at the top of the column for rows that equal Invoice, everything is eliminated except the rows we need.
Next, select the option “Use the first Row as headers” and you are rewarded with this very simple file that can be loaded to a table on a tab in Excel.
Once you have saved the file, you are ready to import the invoice details with Transaction Pro. I used the Invoice import type and set a static value for my item name, but you can pull in item names and map them if that is needed.
The best part is once you’ve built the rules in Power Query and you want to refresh the data, all you have to do is: rerun the QB report, export it over the top of the old source file, open your Excel file, and hit the refresh button in the data tab.
Learning Power Query will enhance your data wrangling skills and make data prep output more predictable.
“Data Wrangling” is a copyrighted image and is used with permission.
