Load file from folder with different column names

[fcipriano]

Hi,

Hoping for your inputs. I tried to load files from folder. The files have different column name, but has the same column number. Basically the first file has the column names, while the rest only has the values. When the file is loaded, only the first file's values loaded, while the rest did not. For some reason, when I first tries it, it worked, but on the next one it did not.

Hoping for your inputs.

Thanks,

[Anonymous · Accepted solution]

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

[fcipriano]

Hi,

You can download the files here;

https://www.dropbox.com/sh/qjl38gu3u8ye677/AAAAGqTCWslP7AJdpoXU01MHa?dl=0

What confuses me is that it worked on my first try, but on my second try it started to show null values for the other files.

Thanks,

[Anonymous]

Hi @fcipriano,

I test without any issue, perhaps you can try my method.

Use steps:

1. Move source data to a empty folder and make sure the file which contains the column header are at the first.

2. Open the query editor and locate to the "LoadFileAndCombine" funtion.

3. Copy the folder path(include the folder name) to path textbox, and click the invoke button.

Regards,

Xiaoxin Sheng

[fcipriano]

Thanks, I will try that!

I was able to do it by loading 2 separate query. 1 query with the header, and 1 without the header. Then I append the 2 queries as new.