I have a folder with hundreds of Excel files (with same root name. Eg.: FileName_YYYYMMDD) that contain similar information, but whose columns may be named differently. For example, one file contains column name 'DealName' and the other 'DealID'.
Important to mention that columns may also be in different sequence. However, there are few possible different 'layouts', so I would like to know if there is a way to load all of them at the same time, creating conditional fields.
In a logical way, I would like to do something like:
LOAD
If (Exists(DealName), DealName, DealID) as DealRef
If (Exists(USDAmount), USDAmount, Amount) as DealAmount
...
FROM [TableName]
Is there any way to doing so?
Thank you all in advance!
