CVS files - How to combine? Different headings & various colums

[KSO_UC]

Hello,

I stucked. I hope someone here can help me. 🙂

I try to combine in Power Query of csv files from one sharepoint folder, but:

- each week we get a new set (around 100 files), where headers change each week, something like

1. set

Name Basic Run CW40 CW41 CW42 CW43 CW45

2. set

Name Basic Run CW42 CW43 CW44 CW45 CW46

3. set

Name Basic Run CW44 CW45 CW46 CW47 CW48

The second problem is that some files in set have varoius amount of first colums and unfortunatelly some of colums have the repeating name but they store different information (that are files generated automaticly by one of our company systems.)

1. set

1 file

Name Basic Run CW40 CW41 CW42 CW43 CW45

2 file

Name Basic Run CW40 CW41 CW42 CW43 CW45

3 file

Name Basic Basic Run Run CW40 CW41 CW42 CW43 CW45

4 file

Name Basic Run Run CW40 CW41 CW42 CW43 CW45

Is there any way to combine that in dynamic way in one query?

Thanks!

[j_ocean]

Problem 1: Select you leading columns and "unpivot other columns" in your transform sample file. Then it should append.

Problem 2a: ("some files in set have varoius amount of first colums") You can add a step in advanced editor, also in transform sample file, to check if a column exists and add one if it doesn't

stepname = if Table.HasColumns(priorstep, "Column Name") then priorstep else Table.AddColumn(priorstep, "Column Name", each "placeholder")

Problem 2b: ("unfortunatelly some of colums have the repeating name but they store different information") You can use a logical colum to try to split this out if you can find a logical test that can properly distinguish between them, and/or use a completely seaprate flow based on which files are which. Other than that it may not be possible and the fix needs to be pushed upstream in the process.

[lbendlin]

You will want to ingest the CSV files separately and unpivot your data. 

You would then end up with a fixed size table that has the week number as an attribute rather than a column.

If you need more assistance please provide a couple sample files.