Excel Power Query prep and best practice & Power BI

Hi all,

I have been an intermittent power query user for the last few years but now I have a job where it is much more valuable and needed. I would not say I am a beginner but I'm certainly no expert either.

What I find myself doing is having to make the same power query lookup multiple times, mainly due to not being sure what I'm going to do, muddling my way through and creating a bit of a "Frankenstein's Monster" in the applied steps. The end result works, but I know its a bit of a mish-mash and could be more efficient and cleaner.

So, to those of you who are experts (or better prepared than I), do you take time to work out what you are going to do before you start, do you list the columns you will keep/discard and note what lookups and transformations you plan to do? Are there any good tips and tricks (or even videos) on best practice, preparation etc.

I also create a lot of governance checks on the data in the columns which makes my file really long and a bit hard to manage. I have considered doing that on a separate page, but then I don't think I can filter back to the main data to see the errors I catch - any advice for that?

Also, is it worth connecting into Power BI for the visuals? I am fine doing pivots and the usual graphs/charts in Excel, but I've heard Power BI is "better". Is it worth investing my time to learn to connect my output data to Power BI?

I will be using power query a lot going forward, and some major projects are reliant on me getting data cleaned, transformed and ready for go-live so I am looking to adopt a better methodology going forward.

Many thanks to any of you who reply.