Note: if you’d like to work along with the steps below, take a moment and download the je.csv file. Some journal entries have been exported from one system, and we need to prepare them for import into another system. The exported journal entries are stored in a CSV file, and before we can import the data into the target system, we need to clean it up. Let’s walk though a quick example to illustrate. Oh, and here is something really cool: We can do this without needing Excel formulas or VBA macros … Wow! We just point and click, my friend. Fortunately, a Get & Transform query can automate this process. Some common transformations include combining and splitting columns, including only selected columns, sorting, filtering, deriving calculated values and flattening data. The preparation, or transformation, step is the one that typically takes the most time for us. The data could be stuff like journal entries, a trial balance, e-commerce transactions, banking activity, payroll data, fixed asset additions or depreciation amounts. Once the data is exported from the source system, we need to prepare it for import into the target system. Let’s pretend for a moment that we need to get data out of one system and into another. We can prepare, or transform, the data by doing things like splitting and combining columns, and adding new calculated columns. We can retrieve, or get, data stored in many types of places-for example in CSV and Excel files, databases and online services. At a high level, they enable us to retrieve data from a variety of sources and prepare it for use as needed. These tools provide new ways to approach tasks, and enable us to do things that were previously time-consuming, impractical or required macros. Formerly available as the “Power Query” add-in, the Get & Transform tools built-in to Excel 2016 for Windows are incredibly powerful. I don’t want to sound overly dramatic here, but this set of capabilities is a game changer. If you haven’t played with the “Get & Transform” commands in Excel 2016 for Windows, they’re probably worth checking out.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |