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

[Anonymous]

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

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.

[ImkeF]

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 

[Anonymous]

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.

[ImkeF]

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.

[Anonymous]

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?

[ImkeF · Accepted solution]

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

[Anonymous]

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.

[ImkeF]

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.

[Anonymous]

Hello Imke!
Great solution but would like to seek your help because I encountered some issues with it.  I have 2 files where the column header changes based on date but it will always be 17 columns (Column B to R). 

First File Headers...
Material nb 02.2020. 03.2020. 04.2020. 05.2020. 06.2020. 07.2020. 08.2020. 09.2020. 10.2020. 11.2020. 12.2020. 01.2021. 02.2021. 03.2021. 04.2021. 05.2021. 06.2021.

2nd File Headers
Material nb 04.2019. 05.2019. 06.2019. 07.2019. 08.2019. 09.2019. 10.2019. 11.2019. 12.2019. 01.2020. 02.2020. 03.2020. 04.2020. 05.2020. 06.2020. 07.2020. 08.2020.

I encountered below error in creating the DATA column. This error happens for the 2nd file where the last date is 08.2020 and it doesn't have 09.2020. 

An error occurred in the ‘’ query. Expression.Error: The column '09.2020.' of the table wasn't found. Details: 09.2020.

Hoping for your help! Thank you!

[Anonymous]

OMG! Nevermind, i was able to fix it by removing the step "change type" that PBI auto creates. IT WORKS PERFECTLY! Thank you! 🙂 

[Anonymous]

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!