How to append only specified columns?

[CloudMonkey]

Hi,

I am appending several tables into 1 big table. I don't need all the columns so I have manually deleted the unnecessary columns after the append. Please can you tell me how to specify which columns I want to append, so that I don't have to then manually delete the columns that aren't required? Or is it not possible?

Thanks,

CM

[bangsheadondesk]

Appending as new (otherwise you could start at second step and add to an existing query):

let
Source = Table.SelectColumns(Table1,{"Column1"}),
#"Appended Query" = Table.Combine({Source, Table.SelectColumns(Table2,{"MatchingColumn"})}),

#"Removed Duplicates" = Table.Distinct(#"AppendedQuery", {"Column1"})

in

#"Removed Duplicates"

You can test it with an index column (sort descending).

[MarcelBeug]

If this is about Power Query, then you can simply select the columns you require, rather than removing the unnecessary columns.

[CloudMonkey]

Hi Marcel, I'm using query editor (accessed via the "Edit Queries" button). Then I'm going to "Home" -> "Append Queries" -> "Append Queries as New", then I see the below screenshot. Please can you tell me how to select which columns to append (currently I can only see how to append entire tables)?:

Thanks,

CM

[MarcelBeug]

Actually you can't select columns during the append step, but you can select prior to, or after, the append.

What I meant, is that you can either:

• remove unwanted columns (select the columns, right-click and choose "Remove", resulting in code using Table.RemoveColumns), or
• select required columns (select the columns, righ-click and choose "Remove Other Columns", resulting in code using Table,SelectColumns).

In below example, from Source table with columns1-10, columns1-3 result after selection/removal:

let
    Source = #table(10,{{1..10}}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "Column2", "Column3"}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"})
in
    #"Removed Columns"

So I was reacting to "I have manually deleted the unnecessary columns after the append" with the intention to state that you don't need to indicate which columns must be deleted; instead you can also indicate which columns need to be kept.

It was not my intention to state that you would be able to select columns during the append step; you can't.

[BradZehr]

You can specify specific colums during the Append step using Table.SelectColumns, as in:

 Table.Combine({#"Query 1", Table.SelectColumns(#"Query 2", "Column Name")})

[mhdilyas2008]

can you please give example here

fatcing the specific column data from multiple tables

[BradZehr]

To append selected columns from two tables:

= Table.Combine({Source, Table.SelectColumns(#"Table 1", "Column 1"), Table.SelectColumns(#"Table 2", "Column 2")})

[mhdilyas2008]

is this correct?

[BradZehr]

[mhdilyas2008]

Thanks for your gr8t support.

It did worked as expacted!!!

[BradZehr]

I think you want more like this:

= Table.Combine({Sheet1, Table.SelectColumns(Sheet2, ColumnName)})

[BradZehr]

= Table.Combine({Source, Table.SelectColumns(#"TableName", {"Column 1", "Column 2"})})

[BradZehr]

Sorry, that was multiple columns from the same table.

[mhdilyas2008]

look like it is from same table.

i have multiple tables, anyway to archive.

Appreciate your support.

[isThisABug]

Hello @BradZehr,

I had the same issue. I followed the same logic that your script has, but I didn't know how to write it. As far as I understand Power Query this is the most efficient way to do what @CloudMonkey  wanted to achieve. Thank you for your help! Already implemented your code and works perfectly 😄

Super kudo for you 👍