Combining multiple csv files from folder, each with different number and/or order of columns

[igonzalezb]

I have a folder with multiple csv files. For example:

Location01.csv:

Timestamp	Flow	Pressure1
1	10	35
2	12	35

Location02.csv:

Timestamp	Pressure1	Pressure2
1	51	24

Location03.csv:

Timestamp	Pressure1	Flow
1	64	13

And so on...

I would like to combine them. For the example the output should look like this:

Filename	Timestamp	Flow	Pressure1	Pressure2
Location01.csv	1	10	35
Location01.csv	2	12	35
Location02.csv	1		51	24
Location03.csv	1	13	64

I tried combining the files, but I always end up with one column missing, depending on wich sample file I choose to do the combining.

[Anonymous · Accepted solution]

an attempt at a quick and very rough solution

The scenario:

3 file csv in the directory: csv1,csv2,csv3

function load in 4 file: the fourth raises error since doesn't exist.

then the query concaTable put the tables together as required(?):

in this form some parameters are hard coded, but it is not difficult to make them dynamic.
I just wanted to propose an idea of how to set up a solution .. if interested, one can work to introduce the management of other aspects.

the code:

function readCsv:

let
    csvNum=(m)=>
    let
    n=Text.From(m), 
    Source = Csv.Document(File.Contents("C:\Users\abcdefg\OneDrive - TIM\MyD2020\BI\loc0" & n & ".csv"),[Delimiter=";",  Encoding=65001, QuoteStyle=QuoteStyle.None]),
    csv = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    in Table.AddColumn(csv, "idx", each "csv"& n)
  
in
   csvNum

query concaTable:

let
    Source = List.Transform({1..4}, each readCsvs(_)),
    cols=List.Union(List.Transform({1..4},each try Table.ColumnNames(Source{_-1}) otherwise {})),
    t= Table.FromRecords(List.Combine(List.Transform({0..2},each Table.ToRecords(Source{_}))),cols,MissingField.UseNull)
    
in
    t

#######edited########

I have removed this line of code which was a step of a previous idea, but is no longer needed here

head=Record.FromList(List.Repeat({""}, List.Count(cols)),cols ),

[edhans]

Glad you found a solution that works for you @igonzalezb 

[edhans]

The combine operation is designed to combine files of similar structure, not just a bunch of files. Each of those files needs different transformations to work, and then you can use Table.Combine in Power Query to make one table from each.

If you could group them by type - say you have 40 files but there are 3 formats. Group them by the 3 formats in folders, then do 3 different Combine operations, transformations, then finally do a Table.Combine(Group1, Group2, Group3) to put them in the final table format.

What you are asking for is certianly possible, but the M code would be complex and potentially fragile, and would not easily be able to handle a new format without crashing.

[igonzalezb]

@edhans Hi thanks for the insight. I ended up solving this by unpivoting the sample file, combining and finally pivoting back the result. It works.