One of the phases of the project for the implementation of ERP information systems is retrieving data. These can be master data (partners, contacts, pricelists, etc.) or financial data. Most of them are retrieved from the company’s old information system, which is often exported as TXT or CSV files. Excel has a mechanism for retrieving entire files and manipulating data, which will be discussed in the text that follows …
Often it happens that different groups of executors fill in the tables that have the same structure, and differ only in the data they contain. Sometimes it is necessary to merge more of such tables into one, and Power Query is the ideal tool for performing such job. Let’s see how we can add the contents of one table to another.
Power Query is indeed powerful Excel add-in that allows retreiving data from different locations, changing them and preparing for further processing. Data sets are retrieved by creating queries to the data source, and the result of each query is stored in a separate table. Sometimes it is necessary to merge the results of two queries. The following text will explain how this works.
Excel has previously offered possibility to import XML files, but it was a rather tedious process because it was necessary to join the corresponding XML schema with the file. Although by definition it is simple, it often happened that my success, and others’ failure, could only be explained by the words: “Excel loves me and does not love you”. You will admit, this is not a fair explanation. Power Query maximally simplified this operation!
If you’ve thought that, when you start using PowerPivot, will get rid of array lookup functions you were deadly wrong! Although the concept of a data model does not go hand in hand with the need to use these class of functions, it is sometimes necessary to search the arrays. To do this you will use the LOOKUPVALUE function, and how to use it, in combination with the WEEKDAY function, you will find out in the text that follows.