Load table with same content but different column names
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:
If (Exists(DealName), DealName, DealID) as DealRef
If (Exists(USDAmount), USDAmount, Amount) as DealAmount