CODEWITHSUNDEEP
excelcsvpowerqueryimport-from-csvget-transform
GTB
GTB

Reputation: 5

Import selected columns from .csv files in Power Query

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.

Upvotes: 0

Views: 1948

Answers (2)

Saif Ulislam
Saif Ulislam

Reputation: 56

  1. Put both files in one folder.
  2. Make sure the Columns names are same the one you want(NAME, ADDRESS, EMAIL). keep other columns as they are.
  3. Open your template or file which you are going import the data from

enter image description here

  1. Go to the menu Data > Get Data> From File >From Folder. select folder that you put the files in. then import
  2. 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 combine 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

Upvotes: 0

Olly
Olly

Reputation: 7891

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"

Upvotes: 0

Related Questions