Import selected columns from .csv files in Power Query-CodePudding

I'm looking to import data with known required column headers, from a variable list of .csv files, some of which have columns I don't need. For example:

File 1 maybe has: NAME, ADDRESS, EMAIL

File 2 has: NAME, ADDRESS, EMAIL, CELL_PHONE

I need to append all csv files into a single table but I only want NAME, ADDRESS, and EMAIL data.

This needs to be a dynamic solution because in future the data supplier may add other new fields in their .csv files, and this is out of my control.

CodePudding user response：

You can combine data from your CSV files then select the columns you want to keep:

let
    Source = Folder.Files("C:\MyFolder\"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
    #"Added CSVdata" = Table.AddColumn(#"Filtered Rows", "CSVdata", each Table.PromoteHeaders(Csv.Document([Content])), type table),
    #"Combined CSVdata" = Table.Combine(#"Added CSVdata"[CSVdata]),
    #"Selected Columns" = Table.SelectColumns(#"Combined CSVdata",{"Project ID", "Material ID", "Material Description"})
in
    #"Selected Columns"

CodePudding user response：

First: Put both files in one folder 2nd: Make sure the Columns names are same the one you want(NAME, ADDRESS, EMAIL). keep other columns as they are. 3rd: Open your template or file which you are going import the data from enter image description here 4th: Go to the menu Data > Get Data> From File >From Folder. select folder that you put the files in. then import 5th: then excel will automatically detect the files you can see in the new window. from there you can click on transform Data button.

enter image description here

you will see the combain files button between content and the first column of the data. click on that and make ok to load the data. your data will be loaded correctly. then you can work on transforming the data however you want to