I have now used Power Query for quite some time, to load data into Power BI models as well as SSAS Tabular Models.
I have one annoying issue, that I haven't found a good solution for, and that's how we can load only selected columns into Power Query from e.g. a SQL table of view. When I connect to SQL server and select the table/view, then it per default load all columns. I know that I can then delete the columns in the Editor, but that's quite annoying since it will then cause an error if I at some point in time remove one of those columns in the view.
I might have a view with 30 columns, but where I only need 10 of them in the model. In that case I remove all of them and that's good for now. Then 6 months later we decide to clean up the view because there are some columns that we know we never used, but if we then delete them, then we'll have to go through all the Tabular models and/or Power BI models that use this view, and delete or alter the "Remove column" step.
Another issue, is also that if I add a new column to the view, then it will automatically be added to a model if I do a refresh - even though it might not be need it in that particular model. Maybe I don't realize it at that time and then I suddenly end up with some new columns in the Tabular Model or Power BI model, that makes absolutely no sense and where the users suddenly report back what they are meant for.
Is it just us that don't get the point on how to work with this, or is it really so stupid designed? What are you guys doing to prevent the above from happening? In the beginning we also tried to write native SQL queries in Power Query, but we find that really cumbersome and clumsy to work with so that's not really a good option either.
Steen Schlüter Persson (DK)