Get Data from Folder wich are not 100% the same

[LaurentZ]

Hi,

I've 4 CSV (with TAB as separator) files each month that I need to append.

So I use the "Import from Folder" feature in PowerQuery.

Those CSV files are extracted from SAP in a way that I can't control the output myself.

They are quite similar:

11 lines with ABAP parameters that I have to remove,

then on line 12 the header (same headers)

then a blank line

then data

My problem is the following: sometime I've a shift of column and a data that is in column D for 3 files, is in column C in the fourth one... the header is the same - but it's still considered as a line of data at thise stage, because it's not promoted yet.

The problem would be solved by doing a thing like that:

1. read the file, remove the 11 lines, promote header, if the column name is empty then remove the entire column,

2. read the second file, do the same, and then append the result to the first file... as header are the same, I won't have column shift anymore and continue with file 3 and 4...

... the problem is that I don't know how to do that or it's even possible?

Do you have a solution ?

Thank you

note that I'm using PowerQuery, not PowerBI so it can't be in M language.

[mahoneypat · Accepted solution]

FYI that PowerQuery in Excel also use M language; the query editor is the same in both.  What you are describing is doable.  Here is one way.

1.  Use the Folder connector to go to the folder with your 4 csv files, and choose Combine & Transform (or something close to that)

2. That will generate a bunch of stuff in the query editor, including a function and a Transform Sample File query (that is tied to that function)

3.  In the Transform Sample File query, do the following transformations

- remove top 11 rows

- promote headers

-filter out blanks (in any one of the columns)

- Ctrl-click the 3 (or 4) columns you want to keep, right click and choose Remove Other Columns

4.  Go to your main query at the bottom (the one with an Invoked Custom Function step that used the function, now updated with your new steps).  If all the remaining columns are the same in the 4 files, all the data should be combined in that query.

5.  Do any additional transformations needed

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[LaurentZ]

Ok, thank you for the solution... I didn't know who could change the transformation file (I'm a beginner).

It works!