Melissa, once again thank you so much!
You are such a legend for helping out on here. I do want to clarify a few points with you.
a) Avoid redundancy in my code (repeating same transformation multiple times)
Could you kindly explain this a little further. This is a huge bug bearer of mine and is one of the largest time consuming bits. I have 60 tabs in excel for individual bonds, when I load them into BI they come in as different tables. They all need to be transformed, that transformation is the exact same of each table but I end up doing it 60+ times. I then append all 60 together into one query so I have all my data in one table.
Based on your comment here that is not the best way to do this. Any links or guidance as to what is a better way to manage all these sheets? One of the biggest issues for me to start a new report is having to do that transformation for hours again. Any hacks or automation would be so welcome.
b) Use multiple dataflows
Now this sounds exactly what I really need to be doing and it is what I was hoping to do. Any good course or structured learning on this within EDNA? I have been methodically working through the structured learning but have yet to come across this yet.
Ideally how I want to use BI going forward having several PBIX that I can call on in one report would be perfect and will avoid my issue of loading up more and more data into one report that shuts it down.