Load file from folder with different column names

Hi fcipriano,

 

You can refer to below steps to merge files from folder:

 

1. Get data from folder.
2. Write a custom function to analysis data and merge them.
3. Use first row as header.

 

Custom functions:

1. AnalysisFile

let
   AnalysisFile = (FilePath as text, Extension as text) => 
    let
        values = {
        {".xlsx", Excel.Workbook(File.Contents(FilePath), true)},
	{".csv", Csv.Document(File.Contents(FilePath))},
	{".txt", Csv.Document(File.Contents(FilePath))}
        {Extension, null}
        },
        Result = List.First(List.Select(values, each _{0}=Extension)){1}
    in
	   Result
in
    AnalysisFile

2. LoadFileAndCombine

let
    LoadFileAndCombine= (FilePath as text) as table => 
    let
        Source = Folder.Files(FilePath),
        #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "aaa" or [Name] = "bbb"),//filter the specify files
        Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each AnalysisFile([Folder Path]&[Content],[Extension])),"Custom"),
        Combine= Table.Combine(Custom[Custom])
in
    Combine
in 
    LoadFileAndCombine

 

Use:

#"Filtered Rows" result.

 

Invoke the function.

let
    Source = LoadFileAndCombine("C:\Users\xxxxxx\Desktop\Test"),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

 

If above not help, can you share the pibx file to test?

 

Regards,

Xiaoxin Sheng