Select columns to load from SQL Server

[steen_p]

Hi,

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.

Regards

Steen

---

Steen Schlüter Persson (DK)

[Mariusz · Accepted solution]

Hi @steen_p 

Why not select the column you want to keep and use right-click option to "Remove other columns", or go to  Home > Choose Columns and select your columns there?

This will list the columns that you want to keep and not the ones that you don't need.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[steen_p]

Hi Mariusz,

Thanks for the answer. I don't think I've ever thought about that was the way to do it 🤔. Maybe it's because we (so far) do all the table and column work in our Datawarehouse, so we never really use any of the tble "manipulation" options in Power Query. But it seems like it''s doing what we are looking for - in a little ackward way in my opinion though, but that's just how it is.

Thanks :-).

Regards

Steen