Reply
DN Member
Member
Posts: 136
Registered: ‎11-21-2016
Accepted Solution

extract data from double line headers, multiple files on folder, unmatching columns

I have a folder with a lot of CSV files like the table below.

 

z.png

 

in each file there is a different list of companies.

in some files there is only one company, in another files I get up to 93 companies. each company in a new row.

items and dates repeats a lot on each row.

 

each month a new CSV will be created. this is why I need to load the whole folder instead of single files.

when I load the folder, company column from first file does not match any other file.

I need somehow to get a list of all companies and their IDs. I am looking for your help.
if there is another topic on this issue, please link it.


Accepted Solutions
Highlighted
Super Contributor
Posts: 1,036
Registered: ‎09-06-2015

Re: extract data from double line headers, multiple files on folder, unmatching columns

Yes, it will be future proof. Please check out this video:

View solution in original post


All Replies
Super Contributor
Posts: 1,036
Registered: ‎09-06-2015

Re: extract data from double line headers, multiple files on folder, unmatching columns

You need 2 queries:

1) Company-table: Keep first 2 rows -> delete first 2 columns -> transpose table

2) Transaction-table: Delete 1st row -> Promote Headers -> Select first 2 columns (item and date) -> Unpivot other columns 

 

 

 

DN Member
Member
Posts: 136
Registered: ‎11-21-2016

Re: extract data from double line headers, multiple files on folder, unmatching columns

I understand what you say, but your idea works only for single files.

I am loading folders.

All the files in the folder contain the same double header, and none of the companies names match.

 

in each file there is a different list of companies.

in some files there is only one company, in another files I get up to 93 companies. each company in a new row.

Super Contributor
Posts: 1,036
Registered: ‎09-06-2015

Re: extract data from double line headers, multiple files on folder, unmatching columns

[ Edited ]

You would need to transform these queries into functions.

Then create 2 custom columns where you call each of these functions.

In a 3rd custom column you can perform the merge / lookup of company names and then you can expand that column.

Delete all other column before and you will get one table with all data in it.

 

DN Member
Member
Posts: 136
Registered: ‎11-21-2016

Re: extract data from double line headers, multiple files on folder, unmatching columns

How to transform queries into functions?
will this be future proof? they must be... I mean, if more files are added to the folder, will these function still handle them? or will I need to create individual functions for each file in the folder?

Highlighted
Super Contributor
Posts: 1,036
Registered: ‎09-06-2015

Re: extract data from double line headers, multiple files on folder, unmatching columns

Yes, it will be future proof. Please check out this video:

DN Member
Member
Posts: 136
Registered: ‎11-21-2016

Re: extract data from double line headers, multiple files on folder, unmatching columns

Thanks a lot for your teaching!
I followed your video instructions step-by-step.

I understood everything, it seems ok. I double checked M language for case-sensitive commands.

No errors at the query editor.

 

But once I try to apply, i got the same error for all 3 tables.

Error:
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC Error: [Expression.Error] The column 'Optional.Value' from the table was not found.. Actual operation was canceled because another operation at the transaction failed.'.

 

Maybe the message above is a little bit different, as I translated it from PT-BR.

I am using win7 64bits. No virtual servers running. Never installed anything related to ODBC or OLE DB.

Super Contributor
Posts: 1,036
Registered: ‎09-06-2015

Re: extract data from double line headers, multiple files on folder, unmatching columns

Pls copy all your queries to a new file and check what's happening there.

Otherwise copy all queries again, paste into a text-editor, copy again and paste the code here for me to check.

DN Member
Member
Posts: 136
Registered: ‎11-21-2016

Re: extract data from double line headers, multiple files on folder, unmatching columns

It worked now!
I just repeated all the steps from your video in a new blank file and now everything is fine!
Once again, thanks a lot for your help!