Can you use the ATTR() aggregation? It is sometimes referred to as an attribute dimension, since it preserves the dimension's values so long as they have the same granularity as the primary data source fields used within the same calculation or visualization.
When setting up the data source you have the option to use SQL to combine multiple data sources with like information using UNION. This makes working with the data much easier.
Here is an excellent article that uses Excel sheets as an example.
If you need to be able to distinguish between the different data sources you can use the advanced tip at the bottom which will allow you to add a column as an identifier.
I tried using ATTR function but even that function requires using aggregate on it while creating a calculated field.
I need to connect to data sources from different servers. So i cannot use an custom sql.