![]() ![]() Download the file via the January article or by clicking here. We will use the same Excel file that was used in the above- referenced article to demonstrate how Power Query works and illustrate its added functionality in more complex situations. While this article uses Excel 2016, the steps are similar in Excel 20. In Excel 2016, it is included under the Data tab in the Get & Transform group. Power Query is available in Excel 2010 and Excel 2013 as a free add- in downloadable from Microsoft's website at. While analysis is best done in PivotTables, Power Query cleans the data for best use by PivotTables. Power Query uses an Excel spreadsheet- like interface, so navigating its features becomes second nature to an experienced Excel user.Ī good way to think about when and why to use Power Query is to consider its relationship to PivotTables. While it can load data from a variety of data sources, such as Excel, comma- separated value (CSV), and text files, and SQL, MySQL, and Oracle server databases, this article focuses on loading data from a CSV or Excel file. Power Query is a powerful tool for modeling and shaping data. While it is a best practice to do as much data scrubbing and transforming in a native application, Power Query can still add value in cleaning raw data and modeling them in a fashion that facilitates further analysis. This article describes how Microsoft Power Query for Excel can be used to easily transform data and be applied in subsequent periods so that very little additional work has to be done to obtain clean data. ![]() Challenges occur when subsequent data are added to the general ledger, because it is time- consuming to repeat all the steps in the scrubbing process. ![]() The approach works very well for most analyses. The January 2017 JofA article " Data Mining Your General Ledger With Excel" presents a step- by- step, formula- based approach to extracting general ledger data and then scrubbing them so that they can be readily analyzed through the use of PivotTables. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |