cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Select columns to load from SQL Server

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Select columns to load from SQL Server

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

 

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Select columns to load from SQL Server

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

 

View solution in original post

Highlighted
Frequent Visitor

Re: Select columns to load from SQL Server

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 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Kudoed Authors