What type of transformations do you need to do? Tableau can do a limited amount with iterating over flat files at the moment, so depending on that you might well be able to do everything you need in Tableau.
If it is too complex then I would create a SQL database (if the data is small enough you might get away with Express edition) to host the data. Then use SSIS to feed the data from the flat files (using a for loop to go through each file) and perform the transformations that you need. This will then create the clean data set you are after.
You could perform both data preparation steps and visualisation in Tableau. For example, you can bring files from multiple data sources via joins and unions, you can split concatenated fields and even pivot data. You can also refresh the data (make Tableau look at new files in your folder) without having to load it. Could you describe the transformation process you take?
As for connecting straight to Power BI to Tableau it might not be possible but I found a blog post where the users connect to Power Pivot https://powerpivotpro.com/2014/11/power-bi-and-tableau-best-friends-forever/
Is this what you are trying to attempt?
Why don't you use any database ETL tool to prepare your data instead of doing it in Power BI? It'll be easy to manage then its up to you use Power BI or Tableau for any visualization out of that.
Hi Jimmy, Here's an explanation of how to connect (Not to Power BI, but to PowerPivot, the father of Power BI)
you can not directly connect Power BI to Tableau.... But....
What you can do is:
1. Create your query in Power BI
2. Open an Excel workbook (Excel 2013 pro plus or excel 2016)
3. Open Power Query (Excel 2013) or Data Conections (Excel 2016)
4. Copy / Paste the query from Power BI to Excel (Yes! You can do that!)
5. Once you load the queries you need to add them to the data model (Power Pivot)... right click on each query and "Load to", then Click on a little square that says "Add to data Model".
6. Done! you replicate your Power BI queries into PowerPivot for Excel.
7. There is a native connector for Power Pivot in Tableau, you can connect to the Excel workbook and you will have the queries created. You can also write DAX code in Excel (With some limitations).
This is how you can reuse Power BI DataModels and Measures (Copying them to Excel Power Query and Power Pivot).
Power Query and Power Pivot are the mother and father of Power BI. You can do your ETL in Power Query for Excel, or do it in PBI and copy/paste the query.
Give it a try, and if this was the answer you were looking for the let me know!