Power Query: Merge and Append

 

Power Query: Merge and Append

Discover the efficiencies and benefits of connecting and combining your data sources to drive your governance and performance measurement!

Don`t be put off by the terminology, Power Query is simply sorting and organising data that you wish to import into your Excel spreadsheet and in doing so you can save a lot of time and reduce the scope for errors. ; The merge and append bit is talking about taking different data sources and adding them together to form a single source from which you can then do all sorts of interesting things!

First of all we need to define what we mean by merge and append. ; To merge a data set in this case means to add one data set to another by adding additional fields.

For example: If we have a table of data that contains details of employee departments we might want to add it to another data table that contains details of their salaries. ; In effect we will create a single table with more data fields in the form of additional columns. ; When merging you need to have what is referred to as a joining data field, this is an identifier that is common to both data tables. ; In our case it will be the employee number, which should be unique.

In the case of append we are taking one data table and adding additional rows of data to the bottom.

For example: If we have a data table of employees from Germany and we wish to add those from Poland then we can do this by appending one to the other. ; The important point to note here is that the field or column headers need to be common to both otherwise additional columns will be added to the table.

So why would we use this feature? ; Imagine you have an organisation where there might be different personnel responsible for inputting or managing the data. ; In large complex organisations it is common for multiple systems to be in use and for data to come from a range of different sources. ; Even when using the same systems the data could be kept separate for a variety of reasons legacy or organisational.

This feature allows you to consolidate the data, clean it up and organise it as you wish for further use.

Wan to learn more? ; Just ask!

The video below takes you through the steps of merging and appending queries:



Comments

Post a Comment

Popular posts from this blog

NKW6 Pt 3 INDEX and MATCH

Image
 Part 3 INDEX and MATCH The last in a set of three looking this time at INDEX and MATCH as a way to search a table and return the contents of a cell.  This combination is a more versatile and perhaps more robust method of looking up data in a table than VLOOKUP. When I look at these two functions I almost always use them together.   It looks somewhat complex but thinking through and breaking it down it becomes clear and there are significant advantages.   First of all indexing is a method of pointing to something so if you imagine a table and you tell the programme which row to go to and which column to align to, you end up and an intersection and the contents of the cell are returned.   This may have it’s uses but it is rather inflexible on its own. By introducing MATCH to replace a specified row or column number you are now making the function so much more flexible and powerful.  The MATCH function looks at a range (either a column or row) and then sees...
Read more

NKW6 PT1 - IF

Image
Introduction I recently pulled together an Excel session to go through the fundamentals of the IF, VLOOKUP, INDEX and MATCH functions.  To do this I started out by putting together a bit of an explanation and then recording, with sample data, how these functions can be used and common pitfalls.  This is the first session. Part 1 IF The IF function can allow you to interrogate the information in a cell or cells and then instruct the programme to return a response, which can be text a number, a cell reference, another function or a calculation.  It sounds rather complex, another way to think about it might be to set yourself a problem for example If the total cost of a project exceeds £3m then I should abandon it otherwise I will go ahead.  How does this look when written out?   ·          =IF ( A1 > 3000000 , ”Abandon” , ”Proceed”) ·          The =IF tells the programme wh...
Read more