Limit columns of import from a spreadsheet

[jgpbi]

I have a report template that generates output based on a spreadsheet. The template is used over and over again but each time with a different source file.

The spreadsheet is created by a well known tool but does not always have the exact same columns.(depending on what version you use)

This means I have source of input that may have a varied number of columns. 

Is there a way to handle columns that may exist in one source file and not another? I can't just delete them in Power Query as the next import will try to delete a column that may not exist.

My only option today is to manually edit the columns of each spreadsheet and add or remove the ones needed.

[amitchandak]

@jgpbi , consider these two functions for rename and column transformation, which can help

https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumnnames

https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumns

you have the option to ignore

Table.TransformColumnNames - https://youtu.be/qz5g35fnHKk

[jgpbi]

@amitchandak  - Thank you. That helps me with a different problem. I did not see an 'ignore' option in that demo.

The issue I am having here is:

Source files are nearly identical. Some have more or less columns.

Source 1 - Column Names - Column-Name1, Column-Name2, Column-Name5, Column-Name6

Source 2 - Column Names - Column-Name1, Column-Name2, Column-Name3, Column-Name6

I only need Columns 1, 2and 6.

In my case there are almost 100 columns in the source and I only need 8 of them.

[amitchandak · Accepted solution]

@jgpbi , Append column in power query can help, it only append if column name are not same, But you can choose what column you finally want

refer example

https://learn.microsoft.com/en-us/powerquery-m/table-combine

Table.Combine(
    {
        Table.FromRecords({[Name = "Bob", Phone = "123-4567"]}),
        Table.FromRecords({[Fax = "987-6543", Phone = "838-7171"]}),
        Table.FromRecords({[Cell = "543-7890"]})
    },
    {"CustomerID", "Name"}

Table.Combine: https://youtu.be/YdqOypKHsRU