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!
When we create a pivot table report, based on statistical data, it’s sometimes convenient to group them into bins so that we can analyze them more easily. In one simple case, where we research the age of the respondents and the answer to the question whether they are smokers or not, you will see how the groups (bins) are made and how can we later use them to create beautiful charts.
The Pivot cache is automatically created each time you insert a Pivot table, and it is an object that replicates the data source based on which Pivot table is being made. Each time you need to change the look of the Pivot table, Excel automatically calls the cache, simply because it’s much faster – it handles local data, stored in the computer’s memory. However, what happens when a synchronization error occurs?
Slicers are great feature, but unfortunately they can not be used to filter traditional Excel reports. If we use the conditional aggregation functions, in order to calculate the desired values, as well as the data validation technique, we can make simple but effective filters by selecting the values from the list. By selecting the criteria from the list, the automatic update of the values in the report is performed. Let’s see how this works!