cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CloudMonkey
Post Prodigy
Post Prodigy

How to append only specified columns?

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

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

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)?:

 

screenshot append.JPG

Thanks,

 

CM

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.

Specializing in Power Query Formula Language (M)

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

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

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 👍  

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors