One of the updates of the Office365 package, within Excel 2016, has brought functions for conditional aggregation of data that find the minimum or maximum values of the range depending on the given conditions. These are the functions MINIFS and MAXIFS, and in the following text I will show you how to use such functions.
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!