Append subset of columns

IanR
Posts: 13
Joined: Wed Aug 09, 2017 11:30 pm

Append subset of columns

Postby IanR » Wed Nov 22, 2017 9:36 pm

Is there a way of appending a subset of the columns from one query into another? As far as I can see the Append function only really works when the two queries have the same number of columns. When this isn't the case, for example when the table that is being appended from has more columns than the table that is being appended to, new columns are created with null values for rows in the appended to table. Corresponding columns also have to be in the same order in each query.

Neither of these conditions fits my data. What I would like to do is select the columns that I would like to append from and select the columns that they are appended to, with no requirement that all columns from the append from table have to be used. Can this be done?

Thanks
Ian

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: Append subset of columns

Postby andredl » Mon Jul 02, 2018 2:10 pm

Hi Ian,

IanR wrote:...As far as I can see the Append function only really works when the two queries have the same number of columns.


This isn't the case, you can have two queries with different numbers of columns, but for the appropriate columns to append correctly, the column heading descriptions have to be exactly the same.

IanR wrote:...when the table that is being appended from has more columns than the table that is being appended to, new columns are created with null values for rows in the appended to table.


Correct - you can always delete the columns you don't want, using the query editor.

IanR wrote:...Corresponding columns also have to be in the same order in each query.


Not the case, the only requirement is that the heading descriptions are the same.


You could try staging your queries. From your first source query (containing the data to which you want to append later), right-click and select "Reference". This will create a new query that refers to the last output of the query it refers to. You can delete the columns you don't want and re-name the ones you want to keep, then load the new query as a connection only (call this query Part1 or similar).

Once done, repeat the exercise for the second source query (containing the data you want to append). Delete unwanted columns and re-name the ones you want to keep, ensuring that the column names are consistent with those in the first source query, and preferably that the number of columns are the same. Call this query Part2 or similar and load as connection only.

Create a fourth query (Data -> Get Data -> Combine Queries -> Append) and append Part2 to Part1, call it something appropriate and load to table/data model.

Your original queries remain intact, and you have your appended data set


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests